Search JSON encoded text in MySQL database using LIKE statement

I am trying to fix a bug in our system but I am not sure how to do it.

The software program saves a json encoded text in the database. The saved encoded text looks like this.

{"en":"\u57fa\u672c\u7684\u306a\u8105\u5a01\u4fdd\u8b77"}

When I try to search the database using a LIKE query it does not return a result.

Following is my query:

SELECT p.* FROM Question p WHERE p.deletedAt IS NULL AND p.title LIKE %\u57fa\u672c% AND p.questionType=3;

This query works perfectly if I use only one character. As an example if I use only \u57fa or \u672c it works perfectly. But combinations doesn't work.

Does anyone have an idea why it is happening?

1 answer

  • answered 2021-11-24 23:41 Dula

    As @Sammitch suggested, escaping the backslashes worked like a charm.

    This query worked.

    SELECT p.* FROM Question p WHERE p.deletedAt IS NULL AND p.title LIKE %\\u57fa\\u672c% AND p.questionType=3;
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum