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 LIKE is case-sensitive. The operator
- 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. Thepattern
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
Parent topic:SQL Operators and Pattern Matching Functions