Post

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.