Jump to content
smn

Sort TEXT values as INT

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!

Share this post


Link to post
Share on other sites

The query you posted returns nothing but a syntax error. There is a missing ")" after the first "signed"

Share this post


Link to post
Share on other sites

Excuse me, this is the correct one: 

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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Might that be because of the different encoding for TEXT field? (It is UTF8, but may be filled with digits as cyrillic letters) 

Edited by smn

Share this post


Link to post
Share on other sites

Looks like the "space" is some other whitespace character.

 

What does

SELECT HEX(price) as hexprice;

give you?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

As far as I can assume, C2 is an " " hex symbol. So should I try:

REPLACE (price, ' ', '')

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

You need to replace the bytes C2A0. Mysql doesn't understand html entities so using   to represent a non-breaking space won't work.

 

Try

REPLACE(price, X'C2A0', '')

Share this post


Link to post
Share on other sites

It works! Thank you very much, Barand! Thank you very much, kicken! Great!

Share this post


Link to post
Share on other sites

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.