LIKE

Define a condition where an entire character string matches (or does not match) a pattern of characters.

Syntax

string [NOT] LIKE | ILIKE pattern [ESCAPE 'character']
string

A character string or an expression that evaluates to a character string.

LIKE | ILIKE
  • Pattern matching with LIKE is case-sensitive. The operator ~~ is equivalent to LIKE.
  • Pattern matching with ILIKE is case-insensitive. The operator ~~* is equivalent to ILIKE.
  • The !~~ and !~~* operators are equivalent to NOT LIKE and NOT ILIKE, respectively.
pattern
The pattern for LIKE and ILIKE must contain constants or query parameters. You cannot apply LIKE and ILIKE to function expressions or table column names. The pattern is a character string, in quotes, that may include the following wildcard characters:
  • A percent symbol (%) matches any set of characters.
  • An underscore (_) matches any one character.
A pattern without wildcards behaves the same as a condition with the equals operator (=).
ESCAPE 'character'
Identifies a single escape character that you can use when you want pattern matching to find actual percent and underscore characters, rather than use these characters as wildcards. The character must be identified here, and it must be placed before the percent or underscore character in the pattern itself.

Examples

This example selects all the rows from the match table where full-time scores begin with 8. 
premdb=# SELECT * FROM match 
WHERE ftscore LIKE '8-%' 
ORDER BY seasonid;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
        8 | 1999-09-19 00:00:00 |   28 |   85 | 8-0     | 4-0
       16 | 2008-05-11 00:00:00 |   27 |   74 | 8-1     | 2-0
       18 | 2010-05-09 00:00:00 |   14 |   95 | 8-0     | 2-0
       20 | 2011-08-28 00:00:00 |   26 |   51 | 8-2     | 3-1
       21 | 2012-12-23 00:00:00 |   14 |   52 | 8-0     | 3-0
(5 rows)
The following example shows the behavior of the escape character. The third query returns only one value because it defines an escape character for the condition ('/') and references it in the LIKE pattern (LIKE '75/%'). Therefore, %, in this case, does not represent a wildcard.
premdb=# SELECT * FROM escapetest;
     c1     
------------
 75p       
 75%       
(2 rows)

premdb=# SELECT * FROM escapetest WHERE c1 LIKE '75%';
     c1     
------------
 75p       
 75%       
(2 rows)

premdb=# SELECT * FROM escapetest WHERE c1 LIKE '75/%' ESCAPE '/';
     c1     
------------
 75%       
(1 row)
You cannot use LIKE to compare one named table column with another:
premdb=# select * from team where name like city;
ERROR:  Pattern found in LIKE must be a string literal