Jump to content

Sort TEXT values as INT


Go to solution Solved by Barand,

Recommended Posts

There is a table with prices (TEXT), I need to sort it as INT. And there is a space in the middle of it. So I use REPLACE for removing spaces and CAST for switching to signed INT. This query:

SELECT price, CAST(REPLACE(price,' ','') AS signed FROM table ORDER BY CAST(REPLACE(price,' ','') AS signed)

Returns this:

 

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 

 

It sorts digits before a space, but do not return the whole price. I need to sort it correctly. 

 

Please help me with this query or at least give me a hint!

Link to comment
https://forums.phpfreaks.com/topic/303197-sort-text-values-as-int/
Share on other sites

That gives

mysql> SELECT price
    ->  , CAST(REPLACE(price,' ','') AS signed) as number
    -> FROM test
    -> ORDER BY CAST(REPLACE(price,' ','') AS signed);
+--------+--------+
| price  | number |
+--------+--------+
| 8 082  |   8082 |
| 8 791  |   8791 |
| 8 791  |   8791 |
| 9 374  |   9374 |
| 9 823  |   9823 |
| 10 186 |  10186 |
| 12 257 |  12257 |
| 12 698 |  12698 |
| 13 959 |  13959 |
| 14 463 |  14463 |
| 14 920 |  14920 |
| 15 132 |  15132 |
| 16 023 |  16023 |
| 16 117 |  16117 |
| 16 606 |  16606 |
+--------+--------+

So, except for "table" being a reserved word and not to be used as a table name, I see no problem.

 

edit: But why are you storing prices as text? Why not just use DECIMAL.

Edited by Barand

No, I've tried this one, but the result is the same. Table name is different, I've changed it. Do you try the query on MySQL? 

 

First, I thought that something was wrong in PHP code, but now I'm checking directly in PHPMyAdmin. And there is no error notes in it. As we can see, REPLACE operator doesn't work correctly, it doesn't remove spaces. It just returns values.

 

I don't know why there are TEXT (not INT or DECIMAL). I was just given this task without permission to change the table fields

hexprice   3133C2A0393539 3136C2A0313137 3331C2A0363939 3138C2A0313537 3231C2A0363136 38C2A0373931 3335C2A0303339 3138C2A0373935 3238C2A0373231 3132C2A0363938 3130C2A0313836 3137C2A0313130 39C2A0333734 3135C2A0313332 3134C2A0393230 38C2A0373931 3238C2A0373231 3137C2A0343039 3230C2A0373333 38C2A0303832 3238C2A0373231 3136C2A0303233 3331C2A0353235 3132C2A0323537 39C2A0383233

Edited by smn

Same result. And it doesn't matter what I include in REPLACE: REPLACE (price, '&nbsp','') or REPLACE (price, ' ','') or REPLACE (price, '194','') or even REPLACE (price, HEX(C2),'') 

 

This was with CAST, without CAST those queries return just the same that is in the price column

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.