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

Question

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

Answer 1

You can try the following:

 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> 

Answer 2

You need to exit the letter character%. The default escape character is the backslash:

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

In this case, the first % sign matches any initial sequence in my_column . The remaining \% interpreted as the letter character%. Thus, the combination: matches all that ends with the% character.

SQLFiddle

Answer 3

As a result, I used regular expressions:

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

However, I would still like to know if this is possible using the LIKE operator / comparison.