Question 374943 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why can't I find results in SQL when using LIKE with special characters such as or ''?

In the database, there is a field that stores JSON, and the content is roughly as follows:

"content":{"systemToken":false,"timezone":"America/Los_Angeles","totalCount":false,"userIds":[]}"

The requirement is to query data where totalCount is true.

SELECT * from task  where  column_a like '%"totalCount%'

This query returns results, but when additional characters are added to the end, there are no results returned.

SELECT * from task  where  column_a like '%"totalCount"%'

How should the SQL be written when there are double quotes (") and backslashes () in the LIKE statement?

Tags:

2

Answers


  1. 0

    Maybe this will help
    SELECT * FROM task WHERE column_a LIKE '%"totalCount":true%'

    Login or Signup to reply.
  2. 0

    The backslash is an escape character in LIKE patterns, so you either have to double the backslashes:

    LIKE '%\"totalCount\"%'

    or specify that you want no escape character:

    LIKE '%"totalCount"%' ESCAPE ''

    See the documentation for details.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search