How can you find a literal percent sign (%) in PostgreSQL using a LIKE query?

postgresql sql-like
magnus picture magnus · Oct 8, 2015 · Viewed 20.6k times · Source

I have character varying entries in a table where some (not all) values contain percentages, e.g., '12%', '97%', etc. I want to find all the values that contain percentages. In other words, I want to find all values that end with a percent sign ('%').

Answer

Rahul Tripathi picture Rahul Tripathi · Oct 8, 2015

You can try like this:

SELECT * FROM my_table  WHERE my_column LIKE '%\%%' ESCAPE '\';

Format

 <like predicate> ::=
      <match value> [ NOT ] LIKE <pattern>
        [ ESCAPE <escape character> ]

 <match value> ::= <character value expression>

 <pattern> ::= <character value expression>

 <escape character> ::= <character value expression>

Related questions

PostgreSQL LIKE query performance variations

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 …

Read More
PostgreSQL: Select data with a like on timestamp field

I am trying to select data from a table, using a "like" on date field "date_checked" (timestamp). But I have this error : SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: timestamp without time zone My request is : SELECT my_…

Read More
LIKE with % on column names

Here is my query that results in a syntax error: SELECT * FROM account_invoice,sale_order WHERE sale_order.name LIKE %account_invoice.origin% The account_invoice.origin field contains the text of sale_order.name, plus other text as …

Read More