smn Posted February 15, 2017 Share Posted February 15, 2017 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! Quote Link to comment Share on other sites More sharing options...
Barand Posted February 15, 2017 Share Posted February 15, 2017 The query you posted returns nothing but a syntax error. There is a missing ")" after the first "signed" Quote Link to comment Share on other sites More sharing options...
smn Posted February 15, 2017 Author Share Posted February 15, 2017 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 15, 2017 Share Posted February 15, 2017 (edited) 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 Quote Link to comment Share on other sites More sharing options...
smn Posted February 15, 2017 Author Share Posted February 15, 2017 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 Quote Link to comment Share on other sites More sharing options...
smn Posted February 15, 2017 Author Share Posted February 15, 2017 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted February 15, 2017 Solution Share Posted February 15, 2017 Looks like the "space" is some other whitespace character. What does SELECT HEX(price) as hexprice; give you? Quote Link to comment Share on other sites More sharing options...
smn Posted February 16, 2017 Author Share Posted February 16, 2017 (edited) 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 Quote Link to comment Share on other sites More sharing options...
smn Posted February 16, 2017 Author Share Posted February 16, 2017 As far as I can assume, C2 is an " " hex symbol. So should I try: REPLACE (price, ' ', '') Quote Link to comment Share on other sites More sharing options...
smn Posted February 16, 2017 Author Share Posted February 16, 2017 Same result. And it doesn't matter what I include in REPLACE: REPLACE (price, ' ','') 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 Quote Link to comment Share on other sites More sharing options...
kicken Posted February 16, 2017 Share Posted February 16, 2017 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', '') Quote Link to comment Share on other sites More sharing options...
smn Posted February 16, 2017 Author Share Posted February 16, 2017 It works! Thank you very much, Barand! Thank you very much, kicken! Great! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.