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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This will handle negative numbers  fractions  string  everything   ORDER BY ISNUMERIC col  DESC  Try Parse col AS decimal 10 2    col

User · Answer

Another and simple way  ORDER BY ABS column name

User · Answer

This works for me   select   from tablename order by cast columnname as int  asc

User · Answer

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

User · Answer

It might help who is looking for the same solution   select   from tablename ORDER BY ABS column name

User · Answer

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

[mysql] SQL order string as number

Examples related to mysql

Examples related to sql

Examples related to string

Examples related to numbers

Examples related to sql-order-by