Mysql "Like" not return two digit values like LAMO10-CodePudding

I have an mysql query like this:

select empcode from employee where empcode like 'LAMO%' order by empcode desc limit 1;

and it's result like this,

But my actual records is,

I need output like this LAMO10

CodePudding user response：

The field empcode is having a data type corresponding to characters. Here, LAMO1 is less than LAMO2 and so on, but observe that the string is compared character by character. Therefore, LAMO10 is smaller than LAMO2 because while comparing from the left, the first 4 characters LAMO are equal, the 5th character 1 in LAMO10 is smaller than the 5th character 2 in LAMO2. So, the order that you would get (if you removed the limit in your query) is:

LAMO9
LAMO8
LAMO7
LAMO6
LAMO5
LAMO4
LAMO3
LAMO2
LAMO10
LAMO1

This explains why you aren't getting your desired output LAMO10. To generate it, you need to order by only the numbers in your string. In this particular dataset, that number you are looking for appears to be everything onward from the 5th character. The corresponding query segment for ordering would be:

ORDER BY CAST(SUBSTR(empcode,5) AS UNSIGNED) DESC

So, putting it in your query:

SELECT empcode 
  FROM employee 
 WHERE empcode like 'LAMO%'
 ORDER BY CAST(SUBSTR(empcode,5) AS UNSIGNED) DESC
 LIMIT 1;

should get you your desired result.

CodePudding user response：

You may use an order by clause which sorts on the numeric component of the employee code:

SELECT empcode
FROM employee
WHERE empcode LIKE 'LAMO%'
ORDER BY CAST(REPLACE(empcode, 'LAMO', '') AS UNSIGNED) DESC
LIMIT 1;