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

mysql sorting a string containing alphanumeric

Hi I have these set $numbser (varchar)

CA 0000002
CA 0000001
CA 10000000
CA 1111111
CA 2222222
CA 20000000
CB 0000001
CB 0000002
CB 1111111

When I use order by numbser, I get these.
SELECT * FROM stock ORDER BY numbser;

CA 0000001
CA 0000002
CA 10000000
CA 1111111
CA 20000000
CA 2222222
CB 0000001
CB 0000002
CB 1111111

How do I get result like this???

CA 0000001
CA 0000002
CA 1111111
CA 2222222
CA 10000000
CA 20000000
CB 0000001
CB 0000002
CB 1111111

Any help????

Tags:

2

Answers


  1. 0

    It seems that you want to sort the alphanumeric values in a way that the numbers are considered for sorting. One way to achieve this is by using a combination of string manipulation and casting. However, please note that this solution might not be optimal, especially if you have a large dataset. You can try the following query to get the desired result:

    SELECT * FROM stock
ORDER BY 
    LEFT(numbser, 2), 
    CAST(SUBSTRING(numbser, 4) AS UNSIGNED);

    Here, LEFT(numbser, 2) is used to sort the values based on the first two characters, and CAST(SUBSTRING(numbser, 4) AS UNSIGNED) is used to extract the numeric part and cast it as an integer for numerical sorting. This query assumes that your data follows the pattern where the first two characters are alphabetic characters and the remaining characters are numeric.

    This should help you achieve the desired result where the alphanumeric values are sorted in the way that you specified.

    Login or Signup to reply.
  2. 0

    To extract numbers and strings from the alphanumeric column, use REGEXP_SUBSTR, then order by string part and then by numeric part

    SELECT *
FROM stock
ORDER BY 
    REGEXP_SUBSTR(numbser,"[^0-9]+"), 
    REGEXP_SUBSTR(numbser,"[0-9]+")+0

    Demo here

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

Back To Top
Search