Jump to content

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


smus

Recommended Posts

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.

Link to comment
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

Link to comment
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.

Link to comment
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)
Link to comment
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
×
×
  • Create New...