LIKE

Pattern-matching conditions

Description

The LIKE operator compares a string expression, such as a column name, with a pattern that uses the wildcard characters % (percent) and _ (underscore). LIKE pattern matching always covers the entire string. To match a sequence anywhere within a string, the pattern must start and end with a percent sign. (Redshift SQL Language reference LIKE).

This grammar is fully supported in Snowflake.

In Snowflake the cases where the escape character is not provided, the default Redshift escape character '\\' will be added for full equivalence.

Grammar Syntax

expression [ NOT ] LIKE | ILIKE pattern [ ESCAPE 'escape_char' ]

Sample Source Patterns

Setup data

Redshift

CREATE TABLE like_ex(name VARCHAR(20));

INSERT INTO like_ex VALUES
  ('John  Dddoe'),
  ('Joe   Doe'),
  ('Joe   Doe '),
  (' Joe   Doe '),
  (' Joe \n Doe '),
  ('John_down'),
  ('Joe down'),
  ('Elaine'),
  (''),
  (null),
  ('1000 times'),
  ('100%');

Like

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT name
  FROM like_ex
  WHERE name LIKE '%Jo%oe%'
  ORDER BY name;

Results

NAME
Joe Doe
Joe Doe
Joe Doe
Joe Doe
John Dddoe

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT name
  FROM like_ex
  WHERE name LIKE '%Jo%oe%' ESCAPE '\\'
  ORDER BY name;

Results

NAME
Joe Doe
Joe Doe
Joe Doe
Joe Doe
John Dddoe

Not like

Input Code:

Redshift

IN -> Redshift_02.sql

SELECT name
  FROM like_ex
  WHERE name NOT LIKE '%Jo%oe%'
  ORDER BY name;

Results

NAME
100%
1000 times
Elaine
Joe down
John_down

dd

Output Code:

Snowflake

OUT -> Redshift_02.sql

SELECT name
  FROM like_ex
  WHERE name NOT LIKE '%Jo%oe%' ESCAPE '\\'
  ORDER BY name;

Results

NAME
100%
1000 times
Elaine
Joe down
John_down

Escape characters

Input Code:

Redshift

IN -> Redshift_03.sql

SELECT name
  FROM like_ex
  WHERE name LIKE '%J%h%^_do%' ESCAPE '^'
  ORDER BY name;
 
SELECT name
 FROM like_ex
 WHERE name LIKE '100\\%'
 ORDER BY 1;

Results

NAME
John_down

NAME
100%

Output Code:

Snowflake

OUT -> Redshift_03.sql

SELECT name
  FROM like_ex
  WHERE name LIKE '%J%h%^_do%' ESCAPE '^'
  ORDER BY name;
 
SELECT name
 FROM like_ex
 WHERE name LIKE '100\\%' ESCAPE '\\'
 ORDER BY 1;

Results

NAME
John_down

NAME
100%

ILike

Input Code:

Redshift

IN -> Redshift_04.sql

SELECT 'abc' LIKE '_B_' AS r1,
       'abc' ILIKE '_B_' AS r2;

Results

R1	R2
FALSE	TRUE

Output Code:

Snowflake

OUT -> Redshift_04.sql

SELECT 'abc' LIKE '_B_' ESCAPE '\\' AS r1,
       'abc' ILIKE '_B_' ESCAPE '\\' AS r2;

Results

R1	R2
FALSE	TRUE

Operators

The following operators are translated as follows:

Redshift	Snowflake
~~	LIKE
!~~	NOT LIKE
~~*	ILIKE
!~~*	NOT ILIKE

Input Code:

Redshift

IN -> Redshift_05.sql

SELECT 'abc' ~~ 'abc' AS r1,
       'abc' !~~ 'a%' AS r2,
       'abc' ~~* '_B_' AS r3,
       'abc' !~~* '_B_' AS r4;

Results

R1	R2	R3	R4
TRUE	FALSE	TRUE	FALSE

Output Code:

Snowflake

OUT -> Redshift_05.sql

SELECT 'abc' LIKE 'abc' ESCAPE '\\' AS r1,
       'abc' NOT LIKE 'a%' ESCAPE '\\' AS r2,
       'abc' ILIKE '_B_' ESCAPE '\\' AS r3,
       'abc' NOT ILIKE '_B_' ESCAPE '\\' AS r4;

Results

R1	R2	R3	R4
TRUE	FALSE	TRUE	FALSE

Known Issues

1. The behavior of fixed char types may differ. Click here for more information.

Related EWIs

There are no known issues.