LIKE

Description

The LIKE expression returns true if the string matches the supplied pattern. (As expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression is NOT (string LIKE pattern).)

LIKE Predicate is functionally equivalent to Snowflake.

Click here to navigate to the PostgreSQL docs page for this syntax.

Grammar Syntax

string [NOT] LIKE pattern [ESCAPE escape-character]

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.

Sample Source Patterns

Setup Data

CREATE TABLE like_ex(
COL1 VARCHAR(30),
COL2 INT
);
 
insert into like_ex values
    ('Jonathan  Joestar', 1),
    ('Joseph   Joestar', 2),
    ('Jotaro Kujo', 3),
    ('Ceasar Zeppeli', 4),
    ('Jolyne Kujo', 5),
    ('', 6),    -- empty string
    (null, 7);

PostgreSQL

Like

select * from like_ex where COL1 ~~ '%Jo%Jo%';;

Result Like

COL1	COL2
Jonathan Joestar	1
Joseph Joestar	2

Ilike

select * from like_ex where COL1 ~~* '%Jo%Jo%';

Result Ilike

COL1	COL2
Jonathan Joestar	1
Joseph Joestar	2
Jotaro Kujo	3
Jolyne Kujo	5

Snowflake

Like

select * from like_ex where COL1 like '%Jo%Jo%';

Result Like

COL1	COL2
Jonathan Joestar	1
Joseph Joestar	2

Ilike

select * from like_ex where COL1 Ilike '%Jo%Jo%';

Result Ilike

COL1	COL2
Jonathan Joestar	1
Joseph Joestar	2
Jotaro Kujo	3
Jolyne Kujo	5