how to sort varchar numeric columns?

Question

I write ... ORDER BY column ASC but my column is VARCHAR and it sorts wrong like

I want to sorting numeric value but its datatype is varchar then how value like this

1.2.840.113619.2.55.3.163578213.42.1355218116.691.1
1.2.840.113619.2.55.3.163578213.42.1355218116.691.10
1.2.840.113619.2.55.3.163578213.42.1355218116.691.100
1.2.840.113619.2.55.3.163578213.42.1355218116.691.101
1.2.840.113619.2.55.3.163578213.42.1355218116.691.2
1.2.840.113619.2.55.3.163578213.42.1355218116.691.20

but i want to in sequence last

1.2.840.113619.2.55.3.163578213.42.1355218116.691.1
1.2.840.113619.2.55.3.163578213.42.1355218116.691.2
1.2.840.113619.2.55.3.163578213.42.1355218116.691.10
1.2.840.113619.2.55.3.163578213.42.1355218116.691.20
1.2.840.113619.2.55.3.163578213.42.1355218116.691.100
1.2.840.113619.2.55.3.163578213.42.1355218116.691.101

and also I have string like this

1.2.840.114257.0.10325113632288210457800001002296133400001
1.2.840.114257.0.10379710976288210457800000002296491200000
1.2.840.114257.0.10328923264288210457800000002296158400001

I also want to sort this ...

That's called [Natural Sort](http://stackoverflow.com/questions/153633/natural-sort-in-mysql). — Álvaro González, Apr 14 '14 at 10:50

score 2 · Accepted Answer · answered Apr 14 '14 at 10:54

2

For your specific example, you can do:

ORDER BY length(col), col

If you have other examples, you might need to "parse" the values using substring_index().

answered Apr 14 '14 at 10:54

Gordon Linoff

1,242,037
58
646
786

Please elaborate your answer ? – Panchotiya Vipul Apr 14 '14 at 11:01

Ashwin Parmar · Answer 2 · 2014-04-14T11:05:26.467

2

I have tried the following and it is working fine. First Remove the "." From the String so you have only numeric and then sort by using ABC() Function so, It will not truncated.

SELECT yourcol AS v FROM test ORDER BY ABS(REPLACE(yourcol, '.', '')), yourcol;

edited Apr 14 '14 at 11:05

answered Apr 14 '14 at 10:58

Ashwin Parmar

3,025
3
26
42

HybrisHelp · Answer 3 · 2014-04-14T11:25:18.733

2

I think you should redesign your schema,

If you have only digits in ur input than go for @Gordon Linoff answer
Or may your input also contain character string(eg: ankit123) and you want sort in order to that char and number also than you may go for this

You have to implementing any Algorithm like this

Lets take eg

1 >1.2.1
2 >1.2.10
3 >1.2.2

Now make all digits equal, Decide any max length(eg: 5) so attach 0 ahead to make all digits with same length

now above string look like

1 >00001.00002.00001
2 >00001.00002.00010
3 >00001.00002.00002

Now if you you fire ORDER BY on this string you get your output in sorted order.

edited Apr 14 '14 at 11:25

answered Apr 14 '14 at 11:06

HybrisHelp

5,518
2
27
65

This string is predefine string we can not change in this string – Panchotiya Vipul Apr 14 '14 at 11:09
you may take temp column store this changed value in that and call order by on that column – HybrisHelp Apr 14 '14 at 11:09
@ankit337: There was no need to edit your answer and recommend Gordon's. Your answer is perfect. The column doesn't contain numbers but strings built from number parts. So one must either redesign the db and store the separate numbers or apply an algorithm such as you describe. Yes, Gordon's answer works in case all numbers parts (except the last) have constant lengths. – Thorsten Kettner Apr 14 '14 at 11:32
`@Thorsetn kettner` Yes,you are right but i think this guys want to sort column which contain only number, that's way i recommend Gordon's answer,It's worth in this case than me. – HybrisHelp Apr 14 '14 at 11:37

how to sort varchar numeric columns?

3 Answers3