Snowflake Pattern Matching

The pattern matching conditions in Snowflake are used to search a string for a given pattern. You can search for the string by matching particular patterns.

A pattern-matching operator searches a string for a pattern specified in the conditional expression and returns either Boolean (true/ false) or matching value if it finds a match. These conditions are particularly important when you need to search string patterns in your database column values. Pattern matching conditions are mainly used in WHERE conditions.

Following are the commonly used pattern matching operators.

LIKE and ILIKE
RLIKE
LIKE ANY and LIKE ALL
CONTAINS

Now, let us check these operators in brief.

Snowflake LIKE

The Snowflake LIKE allows case-sensitive matching of strings based on comparison with a pattern. The pattern uses the wildcard characters % (percent) and _ (underscore). The like compares a string expression such as values in the column.

Following is the syntax of Snowflake LIKE statement.

<string> [NOT] LIKE <pattern> [ ESCAPE <escape> ]

[NOT] LIKE( <string> , <pattern> [ , <escape> ] )

The LIKE pattern matching searches for a pattern in entire string values provided in the input string.

The LIKE performs a case-sensitive match and ILIKE performs a case-insensitive match. You can prepend the NOT keyword to negate the result return by LIKE condition.

select * from S_STUDENT where city LIKE '%angalore';
+----+------+-----------+
| ID | NAME | CITY      |
|----+------+-----------|
|  3 | CCC  | Bangalore |
|  5 | EEE  | Bangalore |
|  6 | FFF  | Mangalore |
+----+------+-----------+

select city like '%angalore' as  match from S_STUDENT;
+-------+
| MATCH |
|-------|
| False |
| True  |
| False |
| True  |
| True  |
+-------+

select * from S_STUDENT where city ILIKE '%angalore';
+----+------+-----------+
| ID | NAME | CITY      |
|----+------+-----------|
|  3 | CCC  | Bangalore |
|  5 | EEE  | Bangalore |
|  6 | FFF  | Mangalore |
|  6 | GGG  | MANGALORE |
+----+------+-----------+

Snowflake RLIKE

The Snowflake RLIKE returns true if the subject matches the specified pattern. Both inputs must be text expressions.

Following is the syntax of RLIKE statement.

RLIKE( <string> , <pattern> [ , <parameters> ] )

<sring> RLIKE <pattern>

The RLIKE is similar to the LIKE function, but with POSIX extended regular expressions instead of SQL LIKE pattern syntax. It supports more complex matching conditions than LIKE.

Following example demostrates the telephone number validation.

select rlike('988-456-7891','[2-9]\\d{2}-\\d{3}-\\d{4}') is_valid_num from dual;

+--------------+
| IS_VALID_NUM |
|--------------|
| True         |
+--------------+

Snowflake LIKE ALL

The Snowflake LIKE ALL allows case-sensitive matching of an input string based on comparison with one or more patterns. You can provide multiple patterns.

Following is the syntax of Snowflake LIKE ALL.

<string> LIKE ALL (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]

The like all function returns the input string if and only if the input string matches all of the patterns.

For example, consider following query.

select * from
(select 'doe chaoe' c1 union select 'don chaoe' c1 union all select 'don' c1) as tmp
where c1 like all( '%do%oe%','d%e');

+-----------+
| C1        |
|-----------|
| doe chaoe |
| don chaoe |
+-----------+

Snowflake LIKE ANY

The Snowflake LIKE ANY allows case-sensitive matching of an input string based on comparison with one or more patterns. You can provide multiple patterns.

Following is the syntax of Snowflake LIKE ANY.

<string> LIKE ANY (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]

The LIKE ANY function returns input string matches any of the patterns.

select * from
(select 'doe chaoe' c1 union select 'don chaoe' c1 union all select 'don' c1) as tmp
where c1 like any ( '%do%oe%','D%e');

+-----------+
| C1        |
|-----------|
| doe chaoe |
| don chaoe |
+-----------+

Snowflake CONTAINS

Snowflake CONTAINS true if string1 contains string2. Both expressions must be text or binary expressions.

Following is the syntax of Snowflake CONTAINS function.

CONTAINS( <string1> , <string2> )

The Returns NULL if either input expression is NULL.

Following example demonstrates the Snowflake CONTAINS function.

>select CONTAINS('doe chaoe', 'oe') as contains from dual;
+----------+
| CONTAINS |
|----------|
| True     |
+----------+