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) withREGEXP_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
| Function | Purpose | Returns |
|---|---|---|
| REGEXP_LIKE(string, pattern) | Check if string matches pattern | Boolean (true/false) |
| REGEXP_SUBSTR(string, pattern, pos, occ, params) | Extract substring matching pattern | String or NULL |
| REGEXP_REPLACE(string, pattern, replacement) | Replace matching parts with replacement | String |
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.
