SQL ORDER chars numerically

Question

I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following:

100
131
200
21
30
31000
etc.

How can I order these chars numerically? Do I need to convert something or is there already an SQL command or function for this?

Thank You.

score 93 · Accepted Answer · answered Jan 28 '09 at 23:26

93

Try this:

ORDER BY CAST(thecolumn AS int)

answered Jan 28 '09 at 23:26

Ray Hidayat

16,055
4
37
43

4

may want to include ISNULL(thecolumn, 0), if there are any nulls. – Russ Cam Jan 28 '09 at 23:29
It won't hurt, but at least in SQL Server, the SELECT statement won't barf if some of the rows have null. – Dana Jan 28 '09 at 23:31
Good idea from @Russ since the sort order of NULLs is not guaranteed by SQL standard: ISNULL will force them to a specific place. I don't usually like adding per-row functions (performance killer) but you're already doing that via CAST anyway. – paxdiablo Jan 28 '09 at 23:58
NULL will sort to top or bottom, but they will all sort together. I believe whether that is the top or bottom is implementation-dependent. – Cade Roux Jan 29 '09 at 00:25
if it's not unsigned, it might get sorted into the middle somewhere if you cast it to 0. Leave it null and guarantee it's at one end or the other. – dkretz Jan 29 '09 at 01:45
4

Will fail if thecolumn value is not a number – Anton Duzenko May 19 '16 at 09:47
What if I want to Order by based on a derived column? This seems to fail on column created like this: `substring(name from '\.(\d*)') as minor` with the error message `column minor does not exist` – Rakesh Mar 29 '17 at 20:01
Worth mentioning that in MySQL, correct function is `ORDER BY CAST(thecolumn AS SIGNED INTEGER)` or `ORDER BY CAST(thecolumn AS UNSIGNED INTEGER)`. – Gyrocode.com May 11 '17 at 14:51
Also worth mentioning that MySQL doesn't accept a 2nd argument for `ISNULL` it only returns 0/1. You have to use `COALESCE(thecolumn, 0)` – Armstrongest May 16 '18 at 20:13

score 30 · Answer 2 · answered Dec 18 '11 at 20:07

30

This Worked for me:

ORDER BY ABS(column_name)

answered Dec 18 '11 at 20:07

Nev

1,529
21
18

1

Nice one. Actually this is a hidden implicit cast :) Not sure about the performance though. – anar khalilov Jan 08 '14 at 12:14
4

This solution is problematic since it takes the absolute value of numbers. Sorting of negative values is therefore probably not what you want. – hnn Jun 24 '14 at 13:42
1

This also worked for me where as the "accepted" answer above did not. Many thanks! – Craigy Craigo Jun 19 '15 at 11:00
Works for MySQL 5.7 – boris1993 Aug 20 '19 at 06:58

score 10 · Answer 3 · answered Dec 18 '18 at 16:58

10

In some situations this might be a good match:

ORDER BY LENGTH(column_name), column_name

Especially if you have a column with either all digits or a mix of letters and digits but all with the same length.

answered Dec 18 '18 at 16:58

Irgendwoanders

101
1
3

I used this to sort by IP addresses – Zac Sep 21 '22 at 09:46

score 2 · Answer 4 · answered Jan 28 '09 at 23:27

2

This is an issue with ordering numeric strings in a "natural sort" (if you lookup "natural sorting" on Google you'll find tons of stuff). Essentially casting the string as int and sorting on the resulting value should fix it.

answered Jan 28 '09 at 23:27

BenAlabaster

39,070
21
110
151

score 2 · Answer 5 · edited Jul 29 '16 at 09:50

The reason for this is that with a char data type, you are sorting the rows as a string.

The idea to ORDER BY CAST() is correct, however performance of this will go down as the number of returned results increases.

If it's only numerical data in this column, the best practice would be to find a suitable numerical data type and change it.

If you really can't change the column and you find yourself having performance issues, I suggest having a sort order column which contains the value cast to an integer (will nulls converted to an appropriate value).

Index the sort order column and ideally, add a trigger to the CHAR column so that inserts or updates to the char value trigger an update to the integer value.

SQL ORDER chars numerically

5 Answers5

Linked