SELECT with where between not working as expected

Question

I'm trying to use this query, to filter between the names C89 to C110.

SELECT id FROM servers WHERE server_name BETWEEN 'C89' AND 'C110'

But this doesn't work. If I change c89 to c90, it at least selects the two servers. It's like it's only using the first 2 numbers in the calculation.

Can anyone help me ? I'm using mariadb 10.5 I'm looking for a solution that doesn't need to add a lot of words into the query. To be as lean as possible for readability

Answer 1

A shorter answer:

... WHERE MID(server, 2) BETWEEN 89 AND 110

Or, if you need to limit to the "C" set of servers:

... WHERE MID(server, 2) BETWEEN 89 AND 110
      AND server LIKE "C%"

Or (to avoid trouble with "24H1"),

... WHERE IF(server LIKE "C%", 
             MID(server, 2) BETWEEN 89 AND 110,
             FALSE)

Oh, now I see another 'bad' server name (INT1). Face it, you need to put some of the burden on the DBA. He must realize that the practical way is to say

... WHERE server BETWEEN 'C89'  AND 'C99'
       OR server BETWEEN 'C100' AND 'C110'

That is, break up the filtering into as many ranges as needed so that string comparisons will work, and do not think numeric.

Answer 2

As mentioned in the comments, when comparing strings, they are compared lexicographically not numerically (even when they contain numerical values). What this means is each character is compared to the comparison string's character of the same index. For example, comparing the strings "10" and "3" would result in "10" preceding "3" because "1" comes before "3". I'm not sure if this is applicable to MariaDB, but in other RDBMS you can usually reference an ASCII table when unsure of what the ordering for one character vs another (notice capital letters preceded lower case).

If all servers' server_name start with letter 'C' then you can use the REPLACE() function in MariaDB to get rid of the 'C' and then cast the string to an INT like so:

SELECT id 
FROM servers 
WHERE REPLACE(server_name, 'C', '')
    BETWEEN 89 AND 110