CODEWITHSUNDEEP
mysqlsql
Pi Network Crytocurrency
Pi Network Crytocurrency

Reputation: 611

How to sort numbers with text numerically in sql?

My table has alphanumeric values. I'd like to order them numerically first but it cannot work.

Below is example table, table name is called method_1 :

id  |    code_name | 
4     200 a
1     100 b
3     100-1 c
9     100-1/1 d
11    100-1/1/1 e
12    100-1/1/10 f
2     100-1/1/11 g
13    100-1/1/3 h
14    100-2 i
20    100-1/2 j
32    100-1/2/1 k

I am using MYSQL Query Browser version 1.2.11, my SQL query is SELECT * FROM method_1 order by code_name , but it cannot sort the number correctly. And I am also using this method SELECT * FROM method_1 order by length(code_name), code_name, still cannot work

Actually I want the expected result is like below the table:

id  |    code_name | 
1     100 b
3     100-1 c
9     100-1/1 d
11    100-1/1/1 e
13    100-1/1/3 h
12    100-1/1/10 f
2     100-1/1/11 g
20    100-1/2 j
32    100-1/2/1 k
14    100-2 i
4     200 a

Using jarlh answer

Answer

Upvotes: 0

Views: 87

Answers (2)

Hooman
Hooman

Reputation: 49

SELECT * 
FROM (SELECT * 
            ,SUBSTRING(code_name,1,3) AS A1 
            ,SUBSTRING(code_name,4, MAX LEN) AS A2  
      FROM T ) AS T_Total 
ORDER BY T_Total.A1 
        ,T_Total.A2

Upvotes: 0

Akina
Akina

Reputation: 42632

WITH 
cte AS (SELECT id, 
               code_name,
               CONCAT('[', REGEXP_REPLACE(SUBSTRING_INDEX(code_name, ' ', 1), '[\-\/ ]', ','), ']') code,
               SUBSTRING_INDEX(code_name, ' ', -1) name
        FROM test)
SELECT id,
       code_name
FROM cte
ORDER BY code->>"$[0]" + 0,
         code->>"$[1]" + 0,
         code->>"$[2]" + 0,
         code->>"$[3]" + 0,
         name;

fiddle

Upvotes: 3

Related Questions