Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use Regexp in Snowflake: Syntax and Examples

In Snowflake, you use REGEXP functions like REGEXP_LIKE, REGEXP_REPLACE, and REGEXP_SUBSTR to work with regular expressions for pattern matching and text manipulation. These functions let you check if a string matches a pattern, extract parts of text, or replace text based on patterns.
📐

Syntax

Snowflake provides several functions to work with regular expressions:

  • REGEXP_LIKE(string, pattern): Returns true if the string matches the pattern.
  • REGEXP_SUBSTR(string, pattern, position, occurrence, parameters): Extracts substring matching the pattern.
  • REGEXP_REPLACE(string, pattern, replacement): Replaces parts of the string matching the pattern with the replacement.

The pattern is a regular expression following standard syntax.

sql
SELECT
  REGEXP_LIKE('hello123', '\\d+') AS has_digits,
  REGEXP_SUBSTR('hello123world', '\\d+') AS digits,
  REGEXP_REPLACE('hello123world', '\\d+', '#') AS replaced_text;
💻

Example

This example shows how to check if a string contains digits, extract those digits, and replace them with a symbol.

sql
SELECT
  REGEXP_LIKE('order2024', '\\d{4}') AS contains_year,
  REGEXP_SUBSTR('order2024', '\\d{4}') AS year_extracted,
  REGEXP_REPLACE('order2024', '\\d{4}', '####') AS masked_order;
Output
CONTAINS_YEAR | YEAR_EXTRACTED | MASKED_ORDER -------------|----------------|-------------- true | 2024 | order####
⚠️

Common Pitfalls

Common mistakes when using regexp in Snowflake include:

  • Not escaping backslashes properly in SQL strings (use double backslashes \\).
  • Using patterns that do not match the intended text format.
  • Confusing REGEXP_LIKE (returns boolean) with REGEXP_SUBSTR (returns string).

Example of wrong and right usage:

sql
/* Wrong: missing double backslash, pattern won't match digits */
SELECT REGEXP_LIKE('abc123', '\d+') AS wrong_match;

/* Right: double backslash to escape in SQL string */
SELECT REGEXP_LIKE('abc123', '\\d+') AS correct_match;
Output
WRONG_MATCH ----------- false CORRECT_MATCH ------------- true
📊

Quick Reference

FunctionPurposeReturns
REGEXP_LIKE(string, pattern)Check if string matches patternBoolean (true/false)
REGEXP_SUBSTR(string, pattern, pos, occ, params)Extract substring matching patternString or NULL
REGEXP_REPLACE(string, pattern, replacement)Replace matching parts with replacementString
✅

Key Takeaways

Use REGEXP_LIKE to test if a string matches a pattern, returning true or false.
Use REGEXP_SUBSTR to extract parts of a string that match a regular expression.
Use REGEXP_REPLACE to substitute matched text with new text.
Always escape backslashes in patterns with double backslashes in SQL strings.
Understand the difference between boolean and string returning regexp functions.