CODEWITHSUNDEEP
postgresqlsql-like
RSNboim
RSNboim

Reputation: 140

POSTGRES 🐘- combine LIKE with exception(E') literal

I'm trying to match a certain text that includes a single quote (i.e. 'company's report...') normally I would have used the E' literal + ' or double single quotes. but when it gets to using the LIKE '%' operator, things got complicated. what is the best approach to match a text with a single quote?

Upvotes: 2

Views: 271

Answers (2)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6736

You can escape single quote with another single quote. Example:

WHERE column LIKE 'RSNboim''s'

From https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character (").

Upvotes: 2

Bart
Bart

Reputation: 124

You can use Dollar-quoted String Constants at Lexical Structure

Your condition should be something like below;

select * from atable
where afield like $$Dianne's %$$

Upvotes: 2

Related Questions