SELECT with where between not working as expected 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
A shorter answer:
... WHERE MID(server, 2) BETWEEN 89 AND 110

Or, if you need to limit to the &quot;C&quot; set of servers:
... WHERE MID(server, 2) BETWEEN 89 AND 110
      AND server LIKE &quot;C%&quot;

Or (to avoid trouble with &quot;24H1&quot;),
... WHERE IF(server LIKE &quot;C%&quot;, 
             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.