CODEWITHSUNDEEP
mysqlsql
lifeng.luck
lifeng.luck

Reputation: 601

MySQL SELECT LIKE space encoding issue?

I have spent several hours to solve this problem, but I can't solve it.

One of column contain value like bellow, and the value contain some blank spaces.

光学传感器 - 光电二极管

I want select all the value equal to "光学传感器 - 光电二极管". But the blank space may be ASCII encoding('\x0a'), or utf8 encoding('\xa0'), So when I execute SQL string LIKE this, will return None.

SELECT * 
FROM  `icbase_icattrvalue` 
WHERE `value` LIKE  '光学传感器 - 光电二极管'

I have try SQL like this:

SELECT *
FROM `icbase_icattrvalue`
WHERE REPLACE( `value` , '\xa0', ' ' ) LIKE REPLACE( '光学传感器 - 光电二极管', '\xa0', ' ' )
LIMIT 0 , 30

or 

SELECT * 
FROM  `icbase_icattrvalue` 
WHERE REPLACE(`value`,  CONVERT(char(160) USING utf8), ' ') 
LIKE  REPLACE('光学传感器 - 电二极管',  CONVERT(char(160) USING utf8), ' ')
LIMIT 0 , 30

or some similar SQL string, But they can't help, they all return None.

What should I to , to select value equal to a string, but ignore such space encoding issue.

I'm sorry for my poor English.

Upvotes: 0

Views: 1004

Answers (2)

lifeng.luck
lifeng.luck

Reputation: 601

Actually this is the right answer, I should replace c2a0, not a0.

NON BREAK SPACE in utf-8 is c2a0

And in python unicode is a0

I get confused with it, Thanks everybody.

SELECT * FROM `icbase_icattrvalue` 
WHERE id = 197193 and 
REPLACE( `value` , UNHEX('c2a0'), ' ' ) = REPLACE( '光学传感器 - 光电二极管', UNHEX('c2a0'),  ' ' );

Upvotes: 1

Avinash Babu
Avinash Babu

Reputation: 6252

As Alexander Gelbukh says in comment i also think that this is due to the incorrect spacing in ur code..

SELECT *
FROM `icbase_icattrvalue`
WHERE REPLACE( `value` , '\xa0', ' ' ) LIKE REPLACE( '光学传感器 - 光电二极管', '\xa0', ' ' )
LIMIT 0 , 30

Upvotes: 1

Related Questions