Search code examples
mysqlsqlversion

Mysql get Version value that between a Version range

I have a query that works as:

@v='2.2.2'
select version FROM app_versions as V where @v between V.min_ver and V.max_ver;

Where the version is expressed as a string of format "x.y.z".

I can't write a query that work right and readable. Is there a better solution for mysql?

Famous test cases (min_ver -> max_ver):
1.10.1 -> 2.3.3
2.2.100 -> 2.2.111
Solution

  • If I understood correctly the question, why not using INET_ATON(expr) ,

    Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian). INET_ATON() returns NULL if it does not understand its argument, or if expr is NULL

    select av.* 
from app_versions  av 
where INET_ATON('2.2.2') between INET_ATON(av.min_ver) and INET_ATON(av.max_ver);

    https://dbfiddle.uk/btp2IhXb