1

I have a table which has id's(varchar) as 

-----------------------------------------
ID        |NAME    |COLUMN1  |COLUMN2   |
-----------------------------------------
11.10     |abcd    |New col  | new col  |
11.10(a)  |abcd    |New col  | New Col 2|
11.50     |abcd    |New COl  | New coli | 
11.50(a1) |abcd    |New col  | New Col 2|
11.50(a2) |abcd    |New col  | New Col 2|
11.300(a) |abcd    |New col  | New Col 2|
11.200(a) |abcd    |New col  | New Col 2|
11.100(a) |abcd    |New col  | New Col 2|
11.40(a)  |abcd    |New col  | New Col 2|

Now, if I am getting the sorted data using:

Select * from table order by length(id) asc,id;

I am getting result like 

11.10
11.50
11.10(a)
11.100(a)
11.200(a)
11.300(a)
11.40(a)
11.50(a)
11.50(a1)

But I want the desired output as 

11.10
11.10(a)
11.40(a)
11.50
11.50(a)
11.50(a2)
11.200(a)
11.300(a)

What would be the appropriate query for the same? I have tried it using CAST but I am getting the desired output.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Anubhav Singh
  • 432
  • 1
  • 5
  • 15
  • 4
    The desired result doesn't have the same ID values as in the sample data... – jarlh Aug 07 '18 at 10:38
  • this could be help https://stackoverflow.com/questions/1868965/varchar-to-number-conversion-for-sorting – hotfix Aug 07 '18 at 10:39
  • Possible duplicate of [MySQL 'Order By' - sorting alphanumeric correctly](https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly) – Chris Aug 07 '18 at 10:57

3 Answers3

2

For your example, you can just use length():

order by length(id), id

A slightly more general approach uses substring_index() and implicit conversion:

order by substring_index(id, '.', 1) + 0,
         substring_index(id, '.', -1) + 0,
         id
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Your desired query maybe this one :

select ID           
  from Table_
 order by cast( SUBSTRING_INDEX(SUBSTRING_INDEX(id, '.',-1), '(', 1) as signed ),
                SUBSTRING_INDEX(SUBSTRING_INDEX(id, '(',-1), ')', 1);

SQL Fiddle Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

Select Convert(Id,UNSIGNED INTEGER) AS num, col1, col2, col3 from Table Order By num This works as Oracle's To_Number, so I would suggest it will be useful

Beck Rakhimov
  • 45
  • 3