Greetings. I use this code here to search among the strings of the largest numeric value in a pair (letter-digit).

SELECT `number` FROM `orders` WHERE `number` RLIKE '^AU.*' order by `number` desc limit 1

It seems to be all right, the request will regularly produce the necessary values, until AU100 appears. The request will ignore it, issuing a maximum AU99. What is the easiest way to get a request to correctly perceive the maximum value?

  • The prefix AU is fixed and does not change? - cheops

2 answers 2

The problem is that you sort the strings, not the numbers. Strings are sorted character by character, and since 9 is greater than 1, the string '99' is greater than the string '100'. In order to solve your problem, you need to select a number from a string using the following construct: CAST(SUBSTRING(number, 3) AS UNSIGNED)

First, we take everything from the original line, except for the first two characters ('AU'), and then we translate what came out of the line into a number.

You can look at the result here: http://sqlfiddle.com/#!9/dbf44a/3

    You can do the following:

     SELECT number FROM orders WHERE number RLIKE '^AU.*' ORDER BY CONVERT(SUBSTRING(number FROM 3), UNSIGNED INTEGER) DESC LIMIT 1

    Here, the prefix AU is cut from the string, and the resulting string-number is converted into a numeric value, which can be sorted as numbers, i.e. when the comparison goes from right to left, not from left to right, as in rows.

    • Only, FROM 3 - the countdown is not from scratch, but from one. - Alexey Ukolov
    • @ Alexey Ukolov, thanks, corrected. - cheops