CODEWITHSUNDEEP
mysqlsorting
wishman
wishman

Reputation: 774

How to sort an alphanumeric column with different lengths in MYSQL?

I have a column with values like below after ordering by it's column name (equipment_no) in ASC and was wondering how to sort it properly as shown in the next list. 

SELECT equipment_no FROM fas_details WHERE equipment_no LIKE 'T%' ORDER BY equipment_no

Please do tell me your suggestions. Thank you.

How mysql sorts

T  - AG - 01
T  - AG - 02
T  - AG - 07
T  - AG - 08
T  - AG - 09
T  - AG - 10
T  - AG - 100
T  - AG - 101
T  - AG - 102
T  - AG - 103
T  - AG - 104
T  - AG - 11
T  - AG - 12
T  - AG - 13
T  - AG - 103
T  - AG - 104
TG - 10
TG - 100
TG - 99

How I want it to be

    T  - AG - 01
    T  - AG - 02
    T  - AG - 07
    T  - AG - 08
    T  - AG - 09
    T  - AG - 10
    T  - AG - 11
    T  - AG - 12
    T  - AG - 13
    T  - AG - 100
    T  - AG - 101
    T  - AG - 102
    T  - AG - 103
    T  - AG - 104
    TG - 10
    TG - 99
    TG - 100

Upvotes: 2

Views: 182

Answers (3)

1000111
1000111

Reputation: 13519

You could try:

SELECT 
equipment_no 
FROM fas_details 
WHERE equipment_no LIKE 'T%' 
ORDER BY SUBSTRING_INDEX(equipment_no,SUBSTRING_INDEX(equipment_no ,'-',-1)+0,1), 
                 SUBSTRING_INDEX(equipment_no ,'-',-1)+0;

Demonstration: You can check demonstration here for four different sets of input 

SET @str := 'T  - AG - 100';

SELECT SUBSTRING_INDEX(@str,'-',-1)+0 AS numberPart,
SUBSTRING_INDEX(@str,SUBSTRING_INDEX(@str,'-',-1)+0,1) AS textPart

Output(for double hyphen):

numberPart     textPart
100            T  - AG -

Output(for single hyphen):

if equipment_no = 'TG - 100'

numberPart      textPart
100                TG -

Note: It will work irrespective of the number of hyphens in your equipment_no column.

Upvotes: 1

Ido Shichor
Ido Shichor

Reputation: 43

You need to extract the part that you want to sort by using string functions. The example below will do it.

order by trim(SUBSTRING_INDEX(equipment_no, '-', -1))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

It looks like you want the last digits to be treated as a number. The trick is handling the different number of hyphens in the name. If there were just one hyphen, you could do:

order by substring_index(equipment_no, '-', 1),
         substring_index(equipment_no, '-', -1) + 0

In your case, this might work:

order by (case when equipment_no not like '%-%-%' 
               then substring_index(equipment_no, '-', 1)
               else substring_index(equipment_no, '-', 2)
          end),
         substring_index(equipment_no, '-', -1) + 0

This handles the 1 or 2 hyphen case, as in your example.

Upvotes: 2

Related Questions