Jump to content


Photo

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


  • Please log in to reply
9 replies to this topic

#1 smus

smus

    Newbie

  • Members
  • Pip
  • 55 posts

Posted 13 February 2017 - 07:54 AM

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?



#2 Ingolme

Ingolme

    Foxy Mod

  • Moderator
  • PipPipPipPipPipPipPip
  • 12,735 posts
  • Gender:Not Telling
  • Interests:Web development, drawing, videogames, foxes.
  • Languages:Javascript, PHP, MySQL

Posted 13 February 2017 - 04:54 PM

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

Experienced web developer and artist. Forum moderator at W3Schools.com and SEGA.com

kya_signature.png


#3 justsomeguy

justsomeguy

    More Human Than Human

  • Moderator
  • PipPipPipPipPipPipPip
  • 29,654 posts
  • Gender:Male
  • Location:Phoenix
  • Languages:Focusing on PHP and JavaScript

Posted 13 February 2017 - 05:09 PM

Why do you think those aren't working? What values are you trying to convert?

Know your history: Babbage | Lovelace | Turing | Hopper | Ritchie
ConTEXT Sublime Text Opera PHP MySQL phpMyAdmin
Use a debugger: Firefox, IE, Chrome, Safari, or Opera
Know the foundations of computer science: algorithms, machine architectures, data structures, etc. Don't just blindly copy techniques from application to application. Know what you are doing, that it works, and why it works. Don't think you know what the industry will be in five years time or what you'll be doing then, so gather a portfolio of general and useful skills. Try to write better, more principled code. Work to make "programming" more of a professional activity and less of a low-level "hacking" activity (programming is also a craft, but not just a craft). Learn from the classics in the field and the better advanced textbooks; don't be satisfied with the easily digested "how to" guides and online documentation - it's shallow.
-- Bjarne Stroustrup

He that teaches himself has a fool for a master.
-- Benjamin Franklin (paraphrased)


#4 smus

smus

    Newbie

  • Members
  • Pip
  • 55 posts

Posted 14 February 2017 - 07:18 AM

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.



#5 smus

smus

    Newbie

  • Members
  • Pip
  • 55 posts

Posted 14 February 2017 - 11:14 AM

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



#6 justsomeguy

justsomeguy

    More Human Than Human

  • Moderator
  • PipPipPipPipPipPipPip
  • 29,654 posts
  • Gender:Male
  • Location:Phoenix
  • Languages:Focusing on PHP and JavaScript

Posted 14 February 2017 - 05:38 PM

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)

Know your history: Babbage | Lovelace | Turing | Hopper | Ritchie
ConTEXT Sublime Text Opera PHP MySQL phpMyAdmin
Use a debugger: Firefox, IE, Chrome, Safari, or Opera
Know the foundations of computer science: algorithms, machine architectures, data structures, etc. Don't just blindly copy techniques from application to application. Know what you are doing, that it works, and why it works. Don't think you know what the industry will be in five years time or what you'll be doing then, so gather a portfolio of general and useful skills. Try to write better, more principled code. Work to make "programming" more of a professional activity and less of a low-level "hacking" activity (programming is also a craft, but not just a craft). Learn from the classics in the field and the better advanced textbooks; don't be satisfied with the easily digested "how to" guides and online documentation - it's shallow.
-- Bjarne Stroustrup

He that teaches himself has a fool for a master.
-- Benjamin Franklin (paraphrased)


#7 smus

smus

    Newbie

  • Members
  • Pip
  • 55 posts

Posted 15 February 2017 - 06:48 AM

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



#8 Ingolme

Ingolme

    Foxy Mod

  • Moderator
  • PipPipPipPipPipPipPip
  • 12,735 posts
  • Gender:Not Telling
  • Interests:Web development, drawing, videogames, foxes.
  • Languages:Javascript, PHP, MySQL

Posted 15 February 2017 - 05:06 PM

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.


Experienced web developer and artist. Forum moderator at W3Schools.com and SEGA.com

kya_signature.png


#9 justsomeguy

justsomeguy

    More Human Than Human

  • Moderator
  • PipPipPipPipPipPipPip
  • 29,654 posts
  • Gender:Male
  • Location:Phoenix
  • Languages:Focusing on PHP and JavaScript

Posted 15 February 2017 - 05:09 PM

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?

Know your history: Babbage | Lovelace | Turing | Hopper | Ritchie
ConTEXT Sublime Text Opera PHP MySQL phpMyAdmin
Use a debugger: Firefox, IE, Chrome, Safari, or Opera
Know the foundations of computer science: algorithms, machine architectures, data structures, etc. Don't just blindly copy techniques from application to application. Know what you are doing, that it works, and why it works. Don't think you know what the industry will be in five years time or what you'll be doing then, so gather a portfolio of general and useful skills. Try to write better, more principled code. Work to make "programming" more of a professional activity and less of a low-level "hacking" activity (programming is also a craft, but not just a craft). Learn from the classics in the field and the better advanced textbooks; don't be satisfied with the easily digested "how to" guides and online documentation - it's shallow.
-- Bjarne Stroustrup

He that teaches himself has a fool for a master.
-- Benjamin Franklin (paraphrased)


#10 smus

smus

    Newbie

  • Members
  • Pip
  • 55 posts

Posted 16 February 2017 - 09:42 AM

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)





1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users