Posted By: Anonymous
Is it possible to convert text into a number within MySQL query? I have a column with an identifier that consists a name and a number in the format of "name-number". The column has VARCHAR type. I want to sort the rows according to the number (rows with the same name) but the column is sorted according to do character order, i.e.
name-1 name-11 name-12 name-2
If I cut off the number, can I convert the ‘varchar’ number into the ‘real’ number and use it to sort the rows? I would like to obtain the following order.
name-1 name-2 name-11 name-12
I cannot represent the number as a separate column.
edited 2011-05-11 9:32
I have found the following solution
... ORDER BY column * 1. If the name will not contain any numbers is it safe to use that solution?
This should work:
SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num FROM table ORDER BY num;