Snowflake: LIKE, iLIKE And rLIKE

Photo by Marten Newhall

Snowflake offers a variety of options that we can use for comparing strings and pattern searching. Let’s have a quick look of LIKE, iLIKE and rLIKE operators, identify their strengths and learn how it can help us from our day to day Snowflake SQL life.

LIKE

Syntax:

-- 1st syntax
<subject> LIKE <pattern> [ ESCAPE <escape> ]

-- 2nd syntax
LIKE( <subject> , <pattern> [ , <escape> ] )

The SQL LIKE operator is used to compare two string values. It also supports wildcards to match very specific string patterns and these are:

1. The percent sign “%” - Matches any sequence of zero or more characters.
2. The underscore “_” - Matches any single character.

The LIKE operator is case-specific. As in the example below, using LIKE, the word ‘Test’ did not turn out to be equal to the word ‘test’ due to case inconsistencies between the two.

figure 1

On case-specific configured DBMS, one of the solutions to this is to ensure that both sides of LIKE clause have the same case specification. In the image below, we ran the same query and used the LOWER() function to match the…