Sort TEXT values as INT

[smn]

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!

[Barand]

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

[smn]

Excuse me, this is the correct one: 

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

Edited February 15, 2017 by smn

[Barand]

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 February 15, 2017 by Barand

[smn]

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

[smn]

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

Edited February 15, 2017 by smn

[Barand]

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

 

What does

SELECT HEX(price) as hexprice;

give you?

[smn]

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 February 16, 2017 by smn

[smn]

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

REPLACE (price, ' ', '')

[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

[kicken]

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', '')

[smn]

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