SQL order string as number

Question

I have numbers saved as VARCHAR to a MySQL database. I can not make them INT due to some other depending circumstances.

It is taking them as character not as number while sorting.

In database I have

1 2 3 4 5 6 7 8 9 10...

On my page it shows ordered list like this:

1 10 2 3 4 5 6 7 8 9

How can I make it appear ordered by numbers ascending?

@Oded You can have some arbitrary data (e.g. settings), which you want to order by its numeric value only in very special cases. — Glutexo, Feb 13 '18 at 06:20
very interesting article - is for MSSQL but should be relatively similar for MySQL: https://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/ — AquaAlex, Apr 30 '19 at 10:56

juergen d · Accepted Answer · 2018-07-11T10:53:42.220

357

If possible you should change the data type of the column to a number if you only store numbers anyway.

If you can't do that then cast your column value to an integer explicitly with

select col from yourtable
order by cast(col as unsigned)

or implicitly for instance with a mathematical operation which forces a conversion to number

select col from yourtable
order by col + 0

BTW MySQL converts strings from left to right. Examples:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

edited Jul 11 '18 at 10:53

answered Aug 04 '12 at 12:15

juergen d

201,996
37
293
362

8

order by col * 1 works perfect. What is the magic behind this? Sorry I am not professional so this might be silly question but How *1 makes it change to number? – Jamol Aug 04 '12 at 12:38
14

MySQL automatically converts the string to a number to make the multiplication with `1` – juergen d Aug 04 '12 at 12:40
1

The first option is most appropriate one, the second has an extra step, but it works non the less. – Manatax May 09 '14 at 19:56
I would be worried about NaN results when using the second option when any non-integer characters may be present. I would opt for the first option, which clearly seems the best way to handle it anyway. – todd Dec 12 '14 at 20:21
7

and if you have mixed string and number and want them both sorted, use "order by col * 1, col" – Hayden Thring Feb 23 '15 at 03:42
except when the column contains text with numbers, e.g. "part-1", "part-2", "part-10", "part-11", "part-3" – ierdna Mar 30 '16 at 00:40
What does the second option do if the string is a decimal, like `1.3` - Does it convert it to a decimal, or does performing a mathematical operation only convert to integers? – superphonic Sep 07 '16 at 14:12
1

@superphonic: The result will be `decimal`. – juergen d Sep 07 '16 at 15:11
1

In Oracle SQL you can use `order by to_number(col)` – Stevoisiak Jun 09 '20 at 17:37
In Oracle SQL, "order by cast(col as unsigned)" seems doesn't work. Though "order by col + 0" and as Stevoisiak mentioned "order by to_number(col)" works. – vinsinraw Sep 30 '20 at 10:20

Velter · Answer 2 · 2018-07-04T18:17:32.453

94

Another way, without using a single cast.

(For people who use JPA 2.0, where no casting is allowed)

select col from yourtable
order by length(col),col

EDIT: only works for positive integers

edited Jul 04 '18 at 18:17

answered Jul 31 '14 at 14:12

Velter

2,080
1
16
19

10

this is a good one, thats worked with column that also contain both int and string – Sruit A.Suk Feb 27 '15 at 21:17
2

This will not work in all cases. Off the top of head, cases that will not work are mixes of whole numbers with; negative numbers, numbers with leading zeros, numbers with fractions and words and numbers combined. – WonderWorker Jun 14 '17 at 14:41
3

great solution without any casting and juggling types around, +1 – Maksim Luzik Nov 22 '18 at 09:26
didn't seem to work at the first sight, but it worked! Thank you! – try2fly.b4ucry Mar 25 '19 at 21:57
how to specify asc or desc order in this sort? – Richard Ochom Jan 06 '22 at 11:10

score 36 · Answer 3 · answered Mar 10 '17 at 07:36

36

Another and simple way

ORDER BY ABS(column_name)

answered Mar 10 '17 at 07:36

Parth Solanki

3,268
2
22
41

score 22 · Answer 4 · answered Oct 15 '15 at 02:41

The column I'm sorting with has any combination of alpha and numeric, so I used the suggestions in this post as a starting point and came up with this.

DECLARE @tmp TABLE (ID VARCHAR(50));
INSERT INTO @tmp VALUES ('XYZ300');
INSERT INTO @tmp VALUES ('XYZ1002');
INSERT INTO @tmp VALUES ('106');
INSERT INTO @tmp VALUES ('206');
INSERT INTO @tmp VALUES ('1002');
INSERT INTO @tmp VALUES ('J206');
INSERT INTO @tmp VALUES ('J1002');

SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum
FROM @tmp
ORDER BY IsNum, LEN(ID), ID;

Results

ID
------------------------
106
206
1002
J206
J1002
XYZ300
XYZ1002

Hope this helps

You may simply use `1 - ISNUMERIC(ID)` instead of `(CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END)` to change 0 to 1 and vise versa. — S.Serpooshan, Dec 30 '18 at 07:34

score 9 · Answer 5 · answered Feb 21 '20 at 08:01

9

It might help who is looking for the same solution.

select * from tablename ORDER BY ABS(column_name)

answered Feb 21 '20 at 08:01

Samir Lakhani

685
10
19

Kind of duplicates this answer https://stackoverflow.com/a/42712796/2518705 posted 3 years earlier – RAM237 Jul 26 '22 at 11:52

score 8 · Answer 6 · answered May 17 '18 at 09:22

8

This works for me.

select * from tablename
order by cast(columnname as int) asc

answered May 17 '18 at 09:22

Patrick De Guzman

81
1
2

can you explain why? – Slavik May 17 '18 at 10:11
the most simple answer, that does exactly what is needed, cast the string to an int , then order by that int in ascending order, no funky stuff :) – denford mutseriwa Apr 09 '19 at 01:31

score 6 · Answer 7 · answered May 19 '14 at 13:48

Another way to convert.

If you have string field, you can transform it or its numerical part the following way: add leading zeros to make all integer strings having equal length.

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( stringfield ) ) , stringfield )

or order by part of a field something like 'tensymbols13', 'tensymbols1222' etc.

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( LEFT( stringfield , 10 ) ) ) , LEFT( stringfield , 10 ) )

score 5 · Answer 8 · edited Oct 12 '18 at 12:52

5

This will handle negative numbers, fractions, string, everything:

ORDER BY ISNUMERIC(col) DESC, Try_Parse(col AS decimal(10,2)), col;

edited Oct 12 '18 at 12:52

slartidan

20,403
15
83
131

answered Mar 30 '18 at 23:43

Amir

63
2
7

score 4 · Answer 9 · edited Jun 24 '16 at 06:49

I was looking also a sorting fields that has letter prefix. Here is what i found out the solution. This might help who is looking for the same solution.

Field Values:

FL01,FL02,FL03,FL04,FL05,...FL100,...FL123456789

select SUBSTRING(field,3,9) as field from table order by SUBSTRING(field,3,10)*1 desc

SUBSTRING(field,3,9) i put 9 because 9 is way enough for me to hold max 9 digits integer values.

So the result will be 123456789 123456788 123456787 ... 100 99 ... 2 1

score -1 · Answer 10 · answered Mar 26 '20 at 20:37

If you are using AdonisJS and have mixed IDs such as ABC-202, ABC-201..., you can combine raw queries with Query Builder and implement the solution above (https://stackoverflow.com/a/25061144/4040835) as follows:

const sortField =
  'membership_id'
const sortDirection =
  'asc'
const subquery = UserProfile.query()
  .select(
    'user_profiles.id',
    'user_profiles.user_id',
    'user_profiles.membership_id',
    'user_profiles.first_name',
    'user_profiles.middle_name',
    'user_profiles.last_name',
    'user_profiles.mobile_number',
    'countries.citizenship',
    'states.name as state_of_origin',
    'user_profiles.gender',
    'user_profiles.created_at',
    'user_profiles.updated_at'
  )
  .leftJoin(
    'users',
    'user_profiles.user_id',
    'users.id'
  )
  .leftJoin(
    'countries',
    'user_profiles.nationality',
    'countries.id'
  )
  .leftJoin(
    'states',
    'user_profiles.state_of_origin',
    'states.id'
  )
  .orderByRaw(
    `SUBSTRING(:sortField:,3,15)*1 ${sortDirection}`,
    {
      sortField: sortField,
    }
  )
  .paginate(
    page,
    per_page
  )

NOTES: In this line: SUBSTRING(:sortField:,3,15)*1 ${sortDirection},

'3' stands for the index number of the last non-numerical character before the digits. If your mixed ID is "ABC-123," your index number will be 4.
'15' is used to catch as any number of digits as possible after the hyphen.
'1' performs a mathematical operation on the substring which effectively casts the substring to a number.

Ref 1: You can read more about parameter bindings in raw queries here: https://knexjs.org/#Raw-Bindings Ref 2: Adonis Raw Queries: https://adonisjs.com/docs/4.1/query-builder#_raw_queries

score -4 · Answer 11 · answered Nov 18 '15 at 11:34

Alter your field to be INT instead of VARCHAR.

I can not make them INT due to some other depending circumstances.

Then fix the depending circumstances first. Otherwise you are working around the real underlying issue. Using a MySQL CAST is an option, but it's masking your bad schema which should be fixed.

SQL order string as number

11 Answers11

Linked

Related