Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

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 the character not as a number while sorting.

In the 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?

1 Answer

0 votes
by (40.4k points)

If possible you can change the data type of the column to a number if you are only storing the numbers. But, if you can't do that then cast your column value to an integer explicitly like this:

select col from yourtable

order by cast(col as unsigned)

Otherwise, implicitly for instance, with a mathematical operation which forces a conversion to the number, you can don this way:

select col from yourtable

order by col + 0

Note: MySQL converts strings from left to right. 

For Example:

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 */

Related questions

0 votes
1 answer
asked Jul 9, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Dec 6, 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

94.1k users

Browse Categories

...