Member-only story

Snowflake: LIKE, iLIKE And rLIKE

Clark Perucho
3 min readAug 8, 2021

--

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…

--

--

Clark Perucho
Clark Perucho

Written by Clark Perucho

Snowflake Data Superhero | Certified SnowPro: Administrator | Teradata 14 Certified Technical Specialist

No responses yet

Write a response