Question 113542 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql "Like" not return two digit values like LAMO10

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,
first result

But my actual records is,
actual records

I need output like this LAMO10

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search