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 the database, I have values like these:

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

But when I sort them by this field I get a result like this:

1 10 2 3 4 5 6 7 8 9

How can I make it get results ordered numerically, 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
– Glutexo, Commented Feb 13, 2018 at 6:20
very interesting article - is for MSSQL but should be relatively similar for MySQL: essentialsql.com/use-sql-server-to-sort-alphanumeric-values — AquaAlex
– AquaAlex, Commented Apr 30, 2019 at 10:56

juergen d · Accepted Answer · 2018-07-11 10:53:42Z

373

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, 2018 at 10:53

answered Aug 4, 2012 at 12:15

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Jamol Over a year ago

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?

juergen d Over a year ago

MySQL automatically converts the string to a number to make the multiplication with 1

Manatax Over a year ago

The first option is most appropriate one, the second has an extra step, but it works non the less.

Hayden Thring Over a year ago

and if you have mixed string and number and want them both sorted, use "order by col * 1, col"

juergen d Over a year ago

@superphonic: The result will be decimal.

|

yivi · Accepted Answer · 2024-08-07 04:48:34Z

102

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

This only works for positive integers

edited Aug 7, 2024 at 4:48

yivi

48.2k18 gold badges133 silver badges157 bronze badges

answered Jul 31, 2014 at 14:12

Velter

2,1992 gold badges16 silver badges20 bronze badges

6 Comments

Sruit A.Suk Over a year ago

this is a good one, thats worked with column that also contain both int and string

WonderWorker Over a year ago

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.

Maksim Luzik Over a year ago

great solution without any casting and juggling types around, +1

try2fly.b4ucry Over a year ago

didn't seem to work at the first sight, but it worked! Thank you!

Richard Ochom Over a year ago

how to specify asc or desc order in this sort?

|

Parth Solanki · Accepted Answer · 2017-03-10 07:36:13Z

40

Another and simple way

ORDER BY ABS(column_name)

answered Mar 10, 2017 at 7:36

Parth Solanki

3,5182 gold badges24 silver badges42 bronze badges

Comments

Losbear · Accepted Answer · 2015-10-15 02:41:37Z

23

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

answered Oct 15, 2015 at 2:41

Losbear

3,3231 gold badge34 silver badges29 bronze badges

1 Comment

S.Serpooshan Over a year ago

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.

Patrick De Guzman · Accepted Answer · 2018-05-17 09:22:40Z

8

This works for me.

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

answered May 17, 2018 at 9:22

Patrick De Guzman

811 silver badge2 bronze badges

2 Comments

Slavik Over a year ago

can you explain why?

denford mutseriwa Over a year ago

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 :)

shukshin.ivan · Accepted Answer · 2014-05-19 13:48:57Z

7

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 ) )

answered May 19, 2014 at 13:48

shukshin.ivan

11.4k4 gold badges58 silver badges73 bronze badges

Comments

Hkachhia · Accepted Answer · 2016-06-24 06:49:08Z

5

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

edited Jun 24, 2016 at 6:49

Hkachhia

4,5458 gold badges44 silver badges79 bronze badges

answered Feb 5, 2016 at 4:22

user2400083

511 silver badge3 bronze badges

Comments

slartidan · Accepted Answer · 2018-10-12 12:52:02Z

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, 2018 at 12:52

slartidan

21.8k17 gold badges99 silver badges139 bronze badges

answered Mar 30, 2018 at 23:43

Amir

833 silver badges7 bronze badges

Comments

Ndianabasi · Accepted Answer · 2020-03-26 20:37:30Z

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

Glen · Accepted Answer · 2015-11-18 11:34:35Z

-5

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.

answered Nov 18, 2015 at 11:34

Glen

1048 bronze badges

Collectives™ on Stack Overflow

SQL order string as number

10 Answers 10

10 Comments

6 Comments

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

10 Comments

6 Comments

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related