SQL order string as number
MysqlSqlStringNumbersSql Order-ByMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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 */
Solution 2 - Mysql
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
Solution 3 - Mysql
Another and simple way
ORDER BY ABS(column_name)
Solution 4 - Mysql
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
Solution 5 - Mysql
This works for me.
select * from tablename
order by cast(columnname as int) asc
Solution 6 - Mysql
It might help who is looking for the same solution.
select * from tablename ORDER BY ABS(column_name)
Solution 7 - Mysql
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 ) )
Solution 8 - Mysql
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
Solution 9 - Mysql
This will handle negative numbers, fractions, string, everything:
ORDER BY ISNUMERIC(col) DESC, Try_Parse(col AS decimal(10,2)), col;
Solution 10 - Mysql
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
Solution 11 - Mysql
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.