Sorting of Alphanumeric data by MySql

Question

I want to sort alphanumeric data by mysql. My data is some typicall type like:

XYZ-1.0-7.0-1
XYZ-1.0-27.0-5.7
XYZ-1.0-20.0-4.6
XYZ-1.0-10.0-2.4
----------------- many more data in this format ------------

I googled and found many links, but none are working.

I want this data as output:

XYZ-1.0-7.0-1
XYZ-1.0-10.0-2.4
XYZ-1.0-20.0-4.6
XYZ-1.0-27.0-5.7

Order will be 1.0 then 1.1 and so on. And it is used in all cases after "-" sign. — steven, May 05 '16 at 04:56

score 0 · Answer 1 · answered May 04 '16 at 15:27

0

There is an answer here on stack overflow with a number of links to possible answers:

MySQL 'Order By' - sorting alphanumeric correctly

answered May 04 '16 at 15:27

Guerry

739
4
10

I already check thi and no one working for this type of data. – steven May 04 '16 at 15:29

score 0 · Answer 2 · answered May 04 '16 at 15:44

0

Remove . and - from the values and sort.

Query

SELECT * FROM your_table_name
ORDER BY CAST((
    REPLACE(REPLACE(
       RIGHT(your_column_name, LENGTH(your_column_name) - 4), '.', ''), '-', '')) 
AS UNSIGNED);

SQL Fiddle Demo

answered May 04 '16 at 15:44

Ullas

11,450
4
33
50

It is not working if one value is : ('XYZ-1.1-7.0-1') – steven May 04 '16 at 16:25
@steven : I have added the value `XYZ-1.1-7.0-1`, and its working fine. Please check this fiddle. http://sqlfiddle.com/#!9/8aec82/1 – Ullas May 04 '16 at 18:00
If we add "XYZ-1.1-7.0-1" then it should comes at the bottom. – steven May 05 '16 at 04:51

score 0 · Answer 3 · answered May 04 '16 at 16:00

A computer can't do what you want automaticly without your help. To sort this values you need to "teach" the query wich is the format of your string. If the format is always the same "XXX-9.9-9.9-9.9" you need to create a query that dinamicly split your string into his values and then sort. Something like:

SELECT * FROM your_table_name ORDER BY SUBSTRING(col,3), substring(col,5,2), substring(col,5,2), substring(col,9,2)

The other problem is that your numeric values aren't always in the same string format: for instance you have in the third part "7.0" and "27.0". If thoose values where formatted to "07.0" and "27.0" would work. To work with thoose values you need to transform them to numbers. Hope this helps.

score 0 · Answer 4 · answered May 04 '16 at 18:13

This query will split your string into parts:

select t.col
    , cast(num1 as unsigned) n1
    , cast(num2 as unsigned) n2
    , cast(num3 as unsigned) n3
    , cast(num4 as unsigned) n4
    , cast(num5 as unsigned) n5
    , cast(num6 as unsigned) n6
from (
    select t.col
        , SUBSTRING_INDEX(t.col, '-', 1) str
        , SUBSTRING_INDEX(t.col, '-', -3) num1
        , SUBSTRING_INDEX(t.col, '.', -3) num2
        , SUBSTRING_INDEX(t.col, '-', -2) num3
        , SUBSTRING_INDEX(t.col, '.', -2) num4
        , SUBSTRING_INDEX(t.col, '-', -1) num5
        , SUBSTRING_INDEX(t.col, '.', -1) num6
    from Table1 t
) t;
-- 

|              col | n1 | n2 | n3 | n4 | n5 | n6 |
|------------------|----|----|----|----|----|----|
|    XYZ-1.0-7.0-1 |  1 |  0 |  7 |  0 |  1 |  0 |
| XYZ-1.0-27.0-5.7 |  1 |  0 | 27 |  0 |  5 |  7 |
| XYZ-1.0-20.0-4.6 |  1 |  0 | 20 |  0 |  4 |  6 |
| XYZ-1.0-10.0-2.4 |  1 |  0 | 10 |  0 |  2 |  4 |

http://sqlfiddle.com/#!9/d3770/1

Use those parts fo sort your result:

select t.col
from (
    select t.col
        , SUBSTRING_INDEX(t.col, '-', 1) str
        , SUBSTRING_INDEX(t.col, '-', -3) num1
        , SUBSTRING_INDEX(t.col, '.', -3) num2
        , SUBSTRING_INDEX(t.col, '-', -2) num3
        , SUBSTRING_INDEX(t.col, '.', -2) num4
        , SUBSTRING_INDEX(t.col, '-', -1) num5
        , SUBSTRING_INDEX(t.col, '.', -1) num6
    from Table1 t
) t
order by str
    , cast(num1 as unsigned)
    , cast(num2 as unsigned)
    , cast(num3 as unsigned)
    , cast(num4 as unsigned)
    , cast(num5 as unsigned)
    , cast(num6 as unsigned)

http://sqlfiddle.com/#!9/d3770/2

You can also eliminate the subquery:

select t.col
from Table1 t
order by SUBSTRING_INDEX(t.col, '-', 1)
    , cast(SUBSTRING_INDEX(t.col, '-', -3) as unsigned)
    , cast(SUBSTRING_INDEX(t.col, '.', -3) as unsigned)
    , cast(SUBSTRING_INDEX(t.col, '-', -2) as unsigned)
    , cast(SUBSTRING_INDEX(t.col, '.', -2) as unsigned)
    , cast(SUBSTRING_INDEX(t.col, '-', -1) as unsigned)
    , cast(SUBSTRING_INDEX(t.col, '.', -1) as unsigned)

http://sqlfiddle.com/#!9/d3770/5

If we add "XYZ-1.1-7.0-1" then it should comes at the bottom. — steven, May 05 '16 at 04:51
@steven - OK, i realized the pattern isn't as clear as i thought. "XYZ-1.1-7.0-1" is missing the last number. Wthout the last point (`.`) my solution won't work, because i count the points from right to left. So what's actually your pattern? — Paul Spiegel, May 05 '16 at 08:21
As in question this is my pattern. Consists of ". and -". But in any format of digits. — steven, May 05 '16 at 08:29
Like some examples of my patterns: XYZ-1.0-7.0-1 , XYZ-11.5-27.2-15.25 any digits pattern followed by "-" seperator. — steven, May 05 '16 at 08:32

Sorting of Alphanumeric data by MySql

4 Answers4