LIKE

Pattern-matching conditions

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

Output Code:

Snowflake

OUT -> Redshift_01.sql

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

Results

Not like

Input Code:

Redshift

IN -> Redshift_02.sql

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

Results

Output Code:

Snowflake

OUT -> Redshift_02.sql

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

Results

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

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

ILike

Input Code:

Redshift

IN -> Redshift_04.sql

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

Results

Output Code:

Snowflake

OUT -> Redshift_04.sql

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

Results

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

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

Known Issues

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

Related EWIs

There are no known issues.