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

  • 3
    When sorting strings, 'C89' comes after 'C110'. If you need to sort it both alphabetically and numerically then you’re going to have to do the work. Commented Feb 2, 2021 at 0:41
  • There is no possible string that would be sorted between 'c89' and 'c110'. You would need to do something like where server_name like 'C%' and cast(substring(server_name,2) as number) between 89 and 110. The actual functions you use would depend on your unknown RDBMS. Commented Feb 2, 2021 at 0:54
  • Normalize servers names, make their numeric part lengths equal by zero-padding. For example, if servers names values numeric parts are not longer than 3 digits then convert C89 to C089. After this BETWEEN will act as needed.
    – Akina
    Commented Feb 2, 2021 at 5:01

2 Answers 2


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,

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.

  • @Freedo - Are there 3 ways to identify a server? id, server, and server_name?
    – Rick James
    Commented Feb 3, 2021 at 17:30
  • @Freedo - There's a lot of TEXT columns; they may lead to performance issues (because of how large columns are stored 'off record'). Anyway, I added a 4th way to do the query; this is simple enough to explain to a DBA and depend on him to "meet the problem halfway" rather than depending on some complex SQL to do all the work.
    – Rick James
    Commented Feb 3, 2021 at 19:04
  • @Freedo - Your example is caught between a rock and a hard space: Server names are arbitrary strings, yet the query is applying some "meaning" (namely 'C' followed by a number) to those strings. If it were "group C, server number 89", then having 2 columns would make the WHERE obvious and efficient. And "24H" could be another 'group' name. Freelance: mysql at rjweb dot org .
    – Rick James
    Commented Feb 4, 2021 at 19:45
  • you can delete your comments now. I'll remove this later
    – Freedo
    Commented Feb 8, 2021 at 4:52

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:

FROM servers 
WHERE REPLACE(server_name, 'C', '')
    BETWEEN 89 AND 110
  • Change server to CAST(REPLACE(server, 'C', '') AS UNSIGNED).
    – Rick James
    Commented Feb 2, 2021 at 4:56
  • @RickJames Sorry I don't follow? Could you please elaborate?
    – J.D.
    Commented Feb 2, 2021 at 13:30
  • If servers C90, C91...C109 are expected to be included (and because they are using BETWEEN I would be very surprised if they weren't) then your query will miss them and only select C90 and C110. @RickJames query gives the numbers of all the servers, as does his answer. Commented Feb 2, 2021 at 13:45
  • @simonatrcl How do you figure that, when I'm casting 89 and 110 to integers before using the BETWEEN clause?
    – J.D.
    Commented Feb 2, 2021 at 14:08
  • @RickJames Right, missed that, thanks!
    – J.D.
    Commented Feb 2, 2021 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.