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?

CodePudding user response：

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 (").

CodePudding user response：

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 %$$