34

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 ('%').

magnus
  • 4,031
  • 7
  • 26
  • 48

3 Answers3

33

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>
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 3
    If you have `standard_conforming_strings` option turned off (can be checked by `show standard_conforming_strings;`), you need to double backslashes: `SELECT * FROM my_table WHERE my_column LIKE '%\\%%' ESCAPE '\\';` And in both cases (regardless of `standard_conforming_strings`) you can use escape string syntax with doubled backslashes: `SELECT * FROM my_table WHERE my_column LIKE E'%\\%%' ESCAPE E'\\';` – Vsevolod Kulaga Apr 05 '17 at 13:35
  • 1
    But what will happens when I use a PreparedStatement? Thank you. – iseki Jan 02 '21 at 16:29
  • What's the purpose of `ESCAPE '\'` clause? The query works fine without that. – Shiva Mar 01 '23 at 15:40
  • There is no need to use "\" specifically. You can use any ASCII char as long as you put it into the ESCAPE clause; – JackHammer Jul 04 '23 at 14:00
19

You have to escape the literal % sign. By default the escape character is the backslash:

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

In this case the first % sign matches any starting sequence in my_column. The remaining \% are interpreted as a literal % character. The combination is therefore: match anything that ends in a % character.

SQLFiddle

Patrick
  • 29,357
  • 6
  • 62
  • 90
4

I ended up using regular expressions:

select * from my_table where my_column ~ '%$';

However, I'd still like to know if it's possible using the LIKE operator/comparison.

magnus
  • 4,031
  • 7
  • 26
  • 48
  • 5
    http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE "*To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character*" –  Oct 08 '15 at 05:59
  • @a_horse_with_no_name is giving documentation for the [PostgreSQL `LIKE` function](https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE), but @magnus is giving a workaround which uses the regex matching, aka [~ operator; Match (regex) case-sensitive](https://www.postgresql.org/docs/6.3/c09.htm) aka [regexp_like](https://www.postgresql.org/docs/current/functions-matching.html#POSIX-VS-XQUERY)... does the percent sign have special meaning in a regex pattern? I think not; so no need to escape it, right? That's why @magnus does it; to avoid escaping %; it's clever. – Nate Anderson Dec 13 '22 at 23:57