CODEWITHSUNDEEP
postgresqlpattern-matchingsql-like
Kevin Whitaker
Kevin Whitaker

Reputation: 13425

Postgres: LIKE query against a string returns no results, though results exist

I feel like I'm missing something really simple here. I'm trying to get a list of results from Postgres, and I know the rows exist since I can see them when I do a simple SELECT *. However, when I try to do a LIKE query, I get nothing back.

Sample data:

{
  id: 1,
  userId: 2,
  cache: '{"dataset":"{\"id\":4,\"name\":\"Test\",\"directory\":\"/data/"...'
};

Example:

SELECT cache FROM storage_table WHERE cache LIKE '%"dataset":"{\"id\":4%';

Is there some escaping I need?

Upvotes: 0

Views: 727

Answers (1)

user330315
user330315

Reputation:

The LIKE operator supports escaping of wildcards (e.g. so that you can search for an underscore or % sign). The default escape character is a backslash.

In order to tell the LIKE operator that you do not want to use the backslash as an ESCAPE character you need to define a different one, e.g. the ~

SELECT cache 
FROM storage_table 
WHERE cache LIKE '%"dataset":"{\"id\":4%' ESCAPE '~';

You can use any character that does not appear in your data, ESCAPE '#' or ESCAPE '§'

SQLFiddle example: http://sqlfiddle.com/#!15/7703f/1

But you should seriously consider upgrading to a more recent Postgres version that fully supports JSON. Not only will your queries be more robust, they can also be a lot faster due to the ability to index a JSON document.

Upvotes: 3

Related Questions