smus Posted February 13, 2017 Share Posted February 13, 2017 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? Link to comment Share on other sites More sharing options...
Ingolme Posted February 13, 2017 Share Posted February 13, 2017 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted February 13, 2017 Share Posted February 13, 2017 Why do you think those aren't working? What values are you trying to convert? Link to comment Share on other sites More sharing options...
smus Posted February 14, 2017 Author Share Posted February 14, 2017 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 More sharing options...
smus Posted February 14, 2017 Author Share Posted February 14, 2017 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted February 14, 2017 Share Posted February 14, 2017 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) Link to comment Share on other sites More sharing options...
smus Posted February 15, 2017 Author Share Posted February 15, 2017 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 More sharing options...
Ingolme Posted February 15, 2017 Share Posted February 15, 2017 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 More sharing options...
justsomeguy Posted February 15, 2017 Share Posted February 15, 2017 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? Link to comment Share on other sites More sharing options...
smus Posted February 16, 2017 Author Share Posted February 16, 2017 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now