Jump to content
Sign in to follow this  
smus

Sorting text as int (ORDER BY CAST(column as signed))

Recommended Posts

I've tried to modify this query, but it's no good:

 

SELECT * FROM column...

 

ORDER BY CAST(column as signed)

ORDER BY ABS(column)

ORDER BY column*1

 

I wonder if there are other ways to sort text values as numbers?

Share this post


Link to post
Share on other sites

You probably can cast it in the SELECT part, then order it. Something like this:

SELECT t.*, CAST(column AS INT) AS sort_field FROM table AS t ORDER BY sort_field

Share this post


Link to post
Share on other sites

Something with the DB field itself, because I've checked the same SQL queries

 

SELECT * FROM 'table' WHERE ORDER BY CAST(column AS signed)

 

SELECT * FROM 'table' WHERE ORDER BY ABS(column)

 

SELECT * FROM 'table' WHERE ORDER BY column*1

 

 

 

in phpmyadmin and they sort the field this way:

 

8 791

8 791

8 082

9 374

9 823

10 186

12 698

12 257

13 959

14 920

14 463

15 132

16 117

16 023

16 606

 

The type of the field is TEXT and it seems to me the second element of each new thousand starts to be sorted from right side, not the left.

Share this post


Link to post
Share on other sites

I was also trying to replace spaces:

SELECT REPLACE (column, ' ', '') AS P FROM table WHERE ORDER BY CAST(column AS signed) 
SELECT REPLACE (column, ' ', '') AS P FROM table WHERE ORDER BY column*1

Result is the same

Share this post


Link to post
Share on other sites

I still don't know what data you're actually trying to convert. What is the output of this:

 

SELECT column, CAST(column AS signed) FROM table ORDER BY CAST(column AS signed)

Share this post


Link to post
Share on other sites

The table contains goods and their prices. The type of price field is TEXT, but I need it to be showed from the smallest price to the highest (as INT).

 

CAST takes thousands and literally "stops" sorting before spaces. The output of the query is:

 

price CAST (price AS signed)

 

8 791 8

8 791 8

8 082 8

9 374 9

9 823 9

10 186 10

12 698 12

12 257 12

13 959 13

14 920 14

14 463 14

15 132 15

16 117 16

16 023 16

16 606 16

Share this post


Link to post
Share on other sites

When you cast a value as INT, it stops at a space because spaces aren't parseable as numbers. Why is there a space?

 

You'll have to do some string operations to remove the space before casting to INT.

Share this post


Link to post
Share on other sites

Why are you storing the numbers as text, anyway? Why not use a numeric column for that and then format the number however you want when you print it?

Share this post


Link to post
Share on other sites

I would be glad if there were integers or decimals, but this is not my code. I was given the task just to add sorting without adding changes to the table.

 

And I've just found the solution. That space was a hex 'C2' symbol and the ORDER BY section have to look like this:

ORDER BY CAST(REPLACE(price, X'C2A0', '') as signed)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×