Home Escape the underscore and the percent when searching by like, ilike expressions in PostgreSQL
Post
Cancel

Escape the underscore and the percent when searching by like, ilike expressions in PostgreSQL

To escape the underscore and the percent to be used in a pattern in like expressions use the escape character (\):

1
2
3
4
5
SELECT * FROM users WHERE name LIKE '\_';
SELECT * FROM users WHERE name LIKE '\%';

SELECT * FROM users WHERE name ILIKE '\_';
SELECT * FROM users WHERE name ILIKE '\%';

In JS, you can use regex and replace function to prepare keyword string:

1
const keyword = keywordFromInput.trim().replace(/([_%\\])/g, '\\$1');
This post is licensed under CC BY 4.0 by the author.

What's the meaning of /gi in a regex?

Useful Docker & docker-compose commands