How do I search "%" (percentage sign) in text using ILIKE operator in PostgreSQL

Question

| id | text  |
|----|-------|
| 1  | AB    |
| 2  | CD%EF |
| 3  | GH    |

I have a text column in a table having a value with a "%" sign.

I wanted to extract that value using the following query —

SELECT text FORM table
WHERE text ILIKE '%%%'

expected Output should be:

CD%EF
1 {row}

actual output returns:

AB
CD%EF
GH
3 {rows}

Answer 1

You can use \% to represent a literal percent sign:

SELECT text FORM table WHERE text ILIKE '%\%%'

Like the documentation says:

To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.