MySQL issue with LIKE statement; JSON encoded content cannot be queried if there are specific Latin characters

Question

I have an issue with Laravel 5.6.39 but I believe it is related to MySql because when I try raw SQL query it works in some cases.

I tried with raw SQL query:

// not working but should 
->where('column', 'LIKE', '%' . 'Za vjen\u010danja' . '%')

// working but it is interesting because example above doesn't work
->where('column', 'LIKE', '%' . 'Za vje' . '%')

In the database array is encoded to JSON:

// initial array
$array = ['Za vjenčanja'];

// retrieved raw from database 
$raw = '["Za vjen\u010danja"]';

It seems that characters like č, ć, ž, etc. are encoded and then query doesn't work but with basic characters from the English alphabet, it works.

*edit I think this question is not duplicate, everything is set up out of the box. Laravel will do all encoding setup. Column type used for this is TEXT utf8mb4_unicode_ci

Both of them shouldn't work (correctly). `whereRaw()` is used as `whereRaw("column LIKE '%searchValue%'")` or as `whereRaw("column LIKE ?", ["%searchValue%"])`. — Paul Spiegel, Feb 16 '19 at 17:29
Possible duplicate of [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Paul Spiegel, Feb 16 '19 at 17:49
@PaulSpiegel I think this question is not duplicate. I cannot find this issue related with Laravel — Aleksandar Milivojsa, Feb 16 '19 at 18:03
Well.. I removed my vote. But I'm still sure, you wouldn't have this issue, if *everything* is configured to use UTF-8. — Paul Spiegel, Feb 16 '19 at 18:08
However you should provide an MCVE: CREATE TABLE statement, INSERT statement and query builder select-where code. — Paul Spiegel, Feb 16 '19 at 18:11
https://stackoverflow.com/questions/45088086/php-7-unicode-escape-syntax-doesnt-work-on-single-quotes Single quote PHP strings apparently do not support escape sequences — tiguchi, Feb 16 '19 at 18:16
Another way to troubleshoot that problem is by enabling MySQL query logging, so you could see for sure what is coming in. I suspect it's literally `\u010` — tiguchi, Feb 16 '19 at 18:17
@tiguchi I will try with query logging but the problem is it stores encoded string so when I try for example `... LIKE "%\u010%"` it returns nothing — Aleksandar Milivojsa, Feb 17 '19 at 13:48

MySQL issue with LIKE statement; JSON encoded content cannot be queried if there are specific Latin characters

0 Answers0