0 votes
1 view
in SQL by (20.3k points)

Is it possible to convert text into a number within MySQL query? I have a column with an identifier that consists of a name and a number in the format of "name-number". The column has a 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 save to use that solution?

1 Answer

0 votes
by (40.3k points)

Use the below query:

SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num

FROM table

ORDER BY num;

Related questions

0 votes
1 answer
asked Jul 10, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 18, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...