Using MySQL LIKE operator for fields encoded in JSON

Question

I've been trying to get a table row with this query: ``` SELECT * FROM `table` WHERE `field` LIKE "%\u0435\u0442\u043e\u0442%" ``` Field itself: ``` Field -------------------------------------------------------------------- \u0435\u0442\u043e\u0442 \u0442\u0435\u043a\u0441\u0442 \u043d\u0430 ``` Although I can't seem to get it working properly. I've already tried experimenting with the backslash character: ``` LIKE "%\u0435\u0442\u043e\u0442%" LIKE "%\\u0435\\u0442\\u043e\\u0442%" ``` But none of them seems to work, as well. I'd appreciate if someone could give a hint as to what I'm doing wrong. Thanks in advance! EDIT Problem solved. Solution: even after correcting the syntax of the query, it didn't return any results. After making the field BINARY the query started working.

Accepted Answer

As documented under String Comparison Functions:

Note

Because MySQL uses C escape syntax in strings (for example, “`
`” to represent a newline character), you must double any “`\`” that you use in `LIKE` strings. For example, to search for “`
`”, specify it as “`\n`”. To search for “`\`”, specify it as “`\\`”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Therefore:

```
SELECT * FROM `table` WHERE `field` LIKE '%\\u0435\\u0442\\u043e\\u0442%'
```

See it on sqlfiddle.