MySql – Sorting a column containing string with numbers

Sorting a column containing string with numbers

If you have wondered how can you sort a column which contains a combination of string and number, please have a look at the following example.

name
====
string12
string124
string1
string342
string9

If you want the display output to be as follows:

name
====
string1
string9
string12
string124
string342

Then the following query can help you.

select name from mytable order by CAST(SUBSTRING(name,LOCATE(‘VIZVRM’,name)+6) AS SIGNED), SUBSTRING(name,-1);

Where
name is the column name in the table that you want it to be displayed in the sorted order.
mytable is the table name.

Leave a Reply