Back

Explore Courses Blog Tutorials Interview Questions
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.4k 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
asked Jan 2 in SQL by Appu (6.1k points)
0 votes
1 answer
asked Dec 31, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer
asked Dec 22, 2020 in SQL by Appu (6.1k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...