Snowflake SQL Tutorial 68 - REGEXP_LIKE and REGEXP_COUNT - Regex 1 (the anchoring rule)

Snowflake SQL · Episode 68

In this post we are going to cover REGEXP_LIKE and REGEXP_COUNT in Snowflake.

These are the two regular-expression functions that only ask questions. REGEXP_LIKE takes a value and a pattern and tells you whether they match. REGEXP_COUNT takes the same two things and tells you how many times the pattern occurs. Neither of them changes anything.

We will start with the simplest test we can write and build it up step by step. Along the way we will cover the one rule in this post that catches everybody who has written regular expressions somewhere else, case sensitivity, the backslash problem, and a real job with three date formats in one column.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back, including the error.

Episode 68 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with a simple REGEXP_LIKE
3. So what is going on? REGEXP_LIKE anchors the pattern for us
4. Can we just write the anchors ourselves?
5. Now let's try REGEXP_COUNT with the same pattern
6. How many times does aa appear in aaaa?
7. Now let's look at case
8. What happens when the pattern needs a literal dot?
9. Can we stop doubling the backslashes?
10. Let's try the same thing on the real email column
11. Now let's count digits three ways
12. Now let's do some real work with three date formats
13. Can one pattern tell us an address is shaped like an address?
14. One last thing: what does Snowflake do with an invalid pattern?

Let's create some sample data

This post uses two tables. EMPLOYEES gives us a column of email addresses, and SALES_RAW gives us the messy text: punctuated phone numbers, mixed-case region codes, and a date column written three different ways.

Copy this into a Snowflake worksheet and run it once.

Sample data

-- Sample data for this post.
-- Run this once. SALES_RAW is deliberately messy: three date formats, four
-- phone formats, currency symbols, mixed-case region codes and one amount
-- that is not a number. Nothing on this page modifies it.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SALES_RAW (
    ROW_ID        INT,
    CUSTOMER_TXT  VARCHAR,
    AMOUNT_TXT    VARCHAR,
    DATE_TXT      VARCHAR,
    REGION_TXT    VARCHAR,
    PHONE_TXT     VARCHAR
);

INSERT INTO SALES_RAW VALUES
    (1, '  Northwind Traders ', '$4,200.00', '2023-01-12',  'us-east',    '(704) 555-0142'),
    (2, 'blue ridge supply',    '890.50',    '01/28/2023',  'US-EAST',    '704-555-0198'),
    (3, 'KESTREL ANALYTICS',    '$6,150',    '14-Feb-2023', 'ca-west',    '+1 416 555 0117'),
    (4, 'Harbour Foods  ',      '2,310.75',  '2023-03-09',  'CA-West',    '604.555.0163'),
    (5, 'Alpine Logistics',     'N/A',       '2023-04-05',  'eu-central', '+41 44 555 0188'),
    (6, ' Delta Print Co',      '$640.00',   '05/21/2023',  'us-east',    '919-555-0124'),
    (7, 'Meridian Health',      '9,250.00',  '14-Aug-2023', 'US-East',    '(617) 555-0179'),
    (8, 'copper kettle ltd',    '$1,080',    '2024-09-29',  'uk-north',   '+44 161 555 0133');

-- The EMPLOYEES table is the one from Episode 1. You already have it if you
-- have been following along. Every statement on this page is a SELECT, and
-- one of them is expected to fail.

Now let's look at the employees.

Query

SELECT EMP_ID, FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ORDER BY EMP_ID;

Result8 rows · 4 columns

EMP_ID	FIRST_NAME	LAST_NAME	EMAIL
1	Aisha	Khan	aisha.khan@example.com
2	Marcus	Reid	marcus.reid@example.com
3	Lena	Fischer	lena.fischer@example.com
4	Tomas	Novak	tomas.novak@example.com
5	Priya	Raman	priya.raman@example.com
6	Daniel	O'Connor	daniel.oconnor@example.com
7	Sofia	Marino	sofia.marino@example.com
8	Grace	Lin	grace.lin@example.com

Eight rows, and every address ends in @example.com. That turns out to matter later, because it is why one of the demonstrations on this page fails to demonstrate anything.

And now the messy table.

Query

SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT, DATE_TXT, REGION_TXT, PHONE_TXT
FROM SALES_RAW ORDER BY ROW_ID;

Result8 rows · 6 columns · scroll →

ROW_ID	CUSTOMER_TXT	AMOUNT_TXT	DATE_TXT	REGION_TXT	PHONE_TXT
1	Northwind Traders	$4,200.00	2023-01-12	us-east	(704) 555-0142
2	blue ridge supply	890.50	01/28/2023	US-EAST	704-555-0198
3	KESTREL ANALYTICS	$6,150	14-Feb-2023	ca-west	+1 416 555 0117
4	Harbour Foods	2,310.75	2023-03-09	CA-West	604.555.0163
5	Alpine Logistics	N/A	2023-04-05	eu-central	+41 44 555 0188
6	Delta Print Co	$640.00	05/21/2023	us-east	919-555-0124
7	Meridian Health	9,250.00	14-Aug-2023	US-East	(617) 555-0179
8	copper kettle ltd	$1,080	2024-09-29	uk-north	+44 161 555 0133

Eight rows again. Have a look at DATE_TXT. It holds 2023-01-12, 01/28/2023 and 14-Feb-2023. Three formats in one column, which is the problem regular expressions are genuinely the right tool for, and the one we finish the page with.

Let's start with a simple REGEXP_LIKE

REGEXP_LIKE takes the value, then the pattern, and gives back TRUE or FALSE.

Let's ask the simplest question we can ask about these addresses. Does the email contain the word example? We will ask it three ways: with the bare word, with .* on both sides of the word, and with ordinary LIKE and % on both sides.

Query

SELECT EMAIL,
       REGEXP_LIKE(EMAIL, 'example')       AS BARE_WORD,
       REGEXP_LIKE(EMAIL, '.*example.*')   AS WITH_DOT_STAR,
       LIKE(EMAIL, '%example%')            AS PLAIN_LIKE
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 4 columns

EMAIL	BARE_WORD	WITH_DOT_STAR	PLAIN_LIKE
aisha.khan@example.com	FALSE	TRUE	TRUE
marcus.reid@example.com	FALSE	TRUE	TRUE
lena.fischer@example.com	FALSE	TRUE	TRUE
tomas.novak@example.com	FALSE	TRUE	TRUE
priya.raman@example.com	FALSE	TRUE	TRUE
daniel.oconnor@example.com	FALSE	TRUE	TRUE
sofia.marino@example.com	FALSE	TRUE	TRUE
grace.lin@example.com	FALSE	TRUE	TRUE

BARE_WORD is FALSE on every row. And every one of those addresses visibly contains the word example.

WITH_DOT_STAR and PLAIN_LIKE are TRUE on every row. Only the bare pattern fails.

A column of FALSE is easy to skim past, so let's run the same three tests again as counts.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(REGEXP_LIKE(EMAIL, 'example'))     AS MATCHED_BARE,
       COUNT_IF(REGEXP_LIKE(EMAIL, '.*example.*')) AS MATCHED_DOT_STAR,
       COUNT_IF(EMAIL LIKE '%example%')            AS MATCHED_LIKE
FROM EMPLOYEES;

Result1 row · 4 columns

ROWS_TOTAL	MATCHED_BARE	MATCHED_DOT_STAR	MATCHED_LIKE
8	0	8	8

Eight rows. Zero matched the bare word. Eight matched the version with .* either side, and eight matched LIKE.

That zero is not a typo in the pattern, and it is not a bug. It is documented behaviour.

So what is going on? REGEXP_LIKE anchors the pattern for us

The Snowflake documentation states it outright: “the function implicitly anchors a pattern at both ends”.

In other words the pattern we wrote is not the pattern that ran. An empty pattern becomes ^$, and ABC becomes ^ABC$.

The one rule to take from this post

REGEXP_LIKE is not a contains test. It is an equals test that understands patterns. Your pattern has to describe the whole value, start to finish, or it returns FALSE. Almost every other engine searches for the pattern anywhere in the string unless you anchor it yourself. Snowflake does the opposite by default.

The documentation's own worked example is five comparisons. Let's run all five in one row.

Query

SELECT REGEXP_LIKE('', '')        AS EMPTY_MATCHES_EMPTY,
       REGEXP_LIKE('ABC', 'ABC')  AS EXACT,
       REGEXP_LIKE('ABCD', 'ABC') AS TRAILING_D_BREAKS_IT,
       REGEXP_LIKE('xABC', 'ABC') AS LEADING_X_BREAKS_IT,
       REGEXP_LIKE('ABCD', 'ABC.') AS PATTERN_COVERS_IT;

Result1 row · 5 columns

EMPTY_MATCHES_EMPTY	EXACT	TRAILING_D_BREAKS_IT	LEADING_X_BREAKS_IT	PATTERN_COVERS_IT
TRUE	TRUE	FALSE	FALSE	TRUE

Read it across. The empty pattern matches the empty string. ABC matches ABC exactly.

ABCD against the pattern ABC is FALSE. One extra character at the end is enough. And xABC is FALSE too, with the extra character at the front.

PATTERN_COVERS_IT is the fix. ABC. describes all four characters, so it matches.

Can we just write the anchors ourselves?

If the anchors are the problem, the first thing most of us try is adding them by hand. Let's see what that does.

Query

SELECT EMAIL,
       REGEXP_LIKE(EMAIL, '.*@example\\.com')   AS WITHOUT_ANCHORS,
       REGEXP_LIKE(EMAIL, '^.*@example\\.com$') AS WITH_ANCHORS
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 3 columns

EMAIL	WITHOUT_ANCHORS	WITH_ANCHORS
aisha.khan@example.com	TRUE	TRUE
marcus.reid@example.com	TRUE	TRUE
lena.fischer@example.com	TRUE	TRUE
tomas.novak@example.com	TRUE	TRUE
priya.raman@example.com	TRUE	TRUE
daniel.oconnor@example.com	TRUE	TRUE
sofia.marino@example.com	TRUE	TRUE
grace.lin@example.com	TRUE	TRUE

Identical columns. The anchors were already there, so adding ^ and $ changed nothing. That is worth knowing mainly so that you do not spend time adding them in the belief that it is the fix.

So how do we write a genuine contains test? Here is the whole list.

What you want	How to write it in Snowflake
Value contains example anywhere	REGEXP_LIKE(C, '.*example.*')
Value contains example, no pattern needed	C LIKE '%example%'
Value contains it, ignoring case	REGEXP_LIKE(C, '.*example.*', 'i') or C ILIKE '%example%'
Value is something pattern-shaped	REGEXP_LIKE(C, pattern), where the anchoring is doing you a favour
How many times it occurs	REGEXP_COUNT(C, 'example'), which we try next

When to use which

If all you need is contains, LIKE says so in fewer characters and does not need the .* armour. Reach for REGEXP_LIKE when the thing you are describing really is a shape, like a date format or a product code. That is the case where the implicit anchoring is exactly what you wanted anyway.

Now let's try REGEXP_COUNT with the same pattern

This is the part that catches people who have already learned the anchoring rule. Let's hand the same pattern, on the same column, to both functions.

Query

SELECT EMAIL,
       REGEXP_LIKE(EMAIL, 'a')  AS LIKE_SAYS,
       REGEXP_COUNT(EMAIL, 'a') AS COUNT_SAYS
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 3 columns

EMAIL	LIKE_SAYS	COUNT_SAYS
aisha.khan@example.com	FALSE	4
marcus.reid@example.com	FALSE	2
lena.fischer@example.com	FALSE	2
tomas.novak@example.com	FALSE	3
priya.raman@example.com	FALSE	4
daniel.oconnor@example.com	FALSE	2
sofia.marino@example.com	FALSE	3
grace.lin@example.com	FALSE	2

LIKE_SAYS is FALSE on every row, because no address is the single letter a.

COUNT_SAYS finds that letter four times in aisha.khan@example.com. One function says the letter is not there and the other says it is there four times, and both are correct.

	REGEXP_LIKE	REGEXP_COUNT
Anchored at both ends	Yes, implicitly	No
Returns	BOOLEAN	A number
'a' against 'aisha.khan@example.com'	FALSE	Four
Natural reading	Does the value match this shape?	How many times does this appear in the value?

That is the pair to remember, and it is the reason the two functions are taught together. Same family, opposite default.

It also gives us a second way to write a contains test. If you do not want to type .* twice, REGEXP_COUNT(C, p) > 0 is a perfectly honest way to spell it.

How many times does aa appear in aaaa?

One question before we leave REGEXP_COUNT, because intuition gets this one wrong. Let's ask it.

Query

SELECT REGEXP_COUNT('aaaa', 'aa')   AS TWO_CHAR_PATTERN,
       REGEXP_COUNT('aaaa', 'a')    AS ONE_CHAR_PATTERN,
       REGEXP_COUNT('aaaa', 'aa', 2) AS STARTING_AT_POSITION_2,
       REGEXP_COUNT('', 'a')        AS EMPTY_SUBJECT,
       REGEXP_COUNT(NULL, 'a')      AS NULL_SUBJECT;

Result1 row · 5 columns

TWO_CHAR_PATTERN	ONE_CHAR_PATTERN	STARTING_AT_POSITION_2	EMPTY_SUBJECT	NULL_SUBJECT
2	4	1	0	NULL

Two, not three. Matches do not overlap. The engine consumes a match and resumes after it, so the pair starting at the second character is never considered.

ONE_CHAR_PATTERN is the control. The single letter is found four times, so the letters really are all there.

The rest of the grid is the edges. A third argument says where to start searching, and starting one character in leaves only one non-overlapping pair. An empty subject counts zero, and a NULL subject counts NULL. So a count you are about to do arithmetic on can still be NULL.

Now let's look at case

Regular expressions in Snowflake are case sensitive unless you say otherwise. Both functions take an optional parameters argument, and 'i' is the one you will use most.

Let's look for a customer name in lower case, then turn the flag on, then upper-case both sides by hand instead.

Query

SELECT ROW_ID, CUSTOMER_TXT,
       REGEXP_LIKE(CUSTOMER_TXT, '.*kestrel.*')      AS LOWER_PATTERN,
       REGEXP_LIKE(CUSTOMER_TXT, '.*kestrel.*', 'i') AS WITH_I_FLAG,
       REGEXP_LIKE(UPPER(CUSTOMER_TXT), '.*KESTREL.*') AS UPPER_BOTH_SIDES
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	CUSTOMER_TXT	LOWER_PATTERN	WITH_I_FLAG	UPPER_BOTH_SIDES
1	Northwind Traders	FALSE	FALSE	FALSE
2	blue ridge supply	FALSE	FALSE	FALSE
3	KESTREL ANALYTICS	FALSE	TRUE	TRUE
4	Harbour Foods	FALSE	FALSE	FALSE
5	Alpine Logistics	FALSE	FALSE	FALSE
6	Delta Print Co	FALSE	FALSE	FALSE
7	Meridian Health	FALSE	FALSE	FALSE
8	copper kettle ltd	FALSE	FALSE	FALSE

KESTREL ANALYTICS is stored in capitals, and LOWER_PATTERN finds nothing anywhere in the column.

Add 'i' as the third argument and WITH_I_FLAG turns TRUE on that row. Force both sides to upper case yourself and UPPER_BOTH_SIDES turns TRUE too. Two routes to the same answer.

Notice that the patterns in that query are already wrapped in .* on both sides. Without that, the anchoring rule applies and all three columns would be FALSE whatever we did about case.

Now let's see what forgetting the flag actually costs, on the region column.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(REGEXP_LIKE(REGION_TXT, 'us-.*'))      AS SENSITIVE,
       COUNT_IF(REGEXP_LIKE(REGION_TXT, 'us-.*', 'i')) AS INSENSITIVE
FROM SALES_RAW;

Result1 row · 3 columns

ROWS_TOTAL	SENSITIVE	INSENSITIVE
8	2	4

Two of the eight rows match with case sensitivity on, and four match with it off. The flag doubled the matches.

So the first version is a report quietly missing half its rows, with no error and no indication that anything was skipped. REGION_TXT in the sample data is why. The same region is written us-east, US-EAST and US-East in one column.

The other way round

UPPER() on both sides works too, and is sometimes clearer. The difference is that the 'i' flag leaves the data alone and only changes the comparison, which matters when you want the original value back in the result.

What happens when the pattern needs a literal dot?

This is where the time actually goes, and it is not really a regular-expression problem. A . in a pattern means any character, so a pattern meant to match a literal full stop matches almost anything in that position.

Let's run the unescaped pattern and the escaped one side by side, on two inputs that differ by one character.

Query

SELECT REGEXP_LIKE('a.c', 'a.c')    AS UNESCAPED_DOT_IS_ANY_CHAR,
       REGEXP_LIKE('abc', 'a.c')    AS SO_THIS_MATCHES_TOO,
       REGEXP_LIKE('a.c', 'a\\.c')  AS ESCAPED_DOT,
       REGEXP_LIKE('abc', 'a\\.c')  AS ESCAPED_DOT_REJECTS_ABC;

Result1 row · 4 columns

UNESCAPED_DOT_IS_ANY_CHAR	SO_THIS_MATCHES_TOO	ESCAPED_DOT	ESCAPED_DOT_REJECTS_ABC
TRUE	TRUE	TRUE	FALSE

UNESCAPED_DOT_IS_ANY_CHAR and SO_THIS_MATCHES_TOO are the trap. The pattern a.c matches both a.c and abc, because the dot is a wildcard.

ESCAPED_DOT and ESCAPED_DOT_REJECTS_ABC are the fix. The escaped pattern matches the literal dot and correctly rejects abc.

Why the doubling exists

The escaped pattern in that query is written with two backslashes, and the regular-expression engine only ever sees one. A SQL string literal processes escapes before the function is called, so it consumes the first backslash. Every backslash you want the pattern to contain has to be written twice inside ordinary single quotes. That is why patterns like '\\d{4}-\\d{2}' look so unlike the regex you would write in any other language.

Can we stop doubling the backslashes?

Yes. Snowflake has a second string-literal syntax, $$...$$, which passes its contents through untouched. Inside it we write one backslash, exactly as we would anywhere else.

Let's run both spellings side by side on the same two inputs.

Query

SELECT REGEXP_LIKE('a.c', 'a\\.c')    AS DOUBLED_BACKSLASH,
       REGEXP_LIKE('a.c', $$a\.c$$)   AS DOLLAR_QUOTED,
       REGEXP_LIKE('abc', 'a\\.c')    AS DOUBLED_ON_ABC,
       REGEXP_LIKE('abc', $$a\.c$$)   AS DOLLAR_QUOTED_ON_ABC;

Result1 row · 4 columns

DOUBLED_BACKSLASH	DOLLAR_QUOTED	DOUBLED_ON_ABC	DOLLAR_QUOTED_ON_ABC
TRUE	TRUE	FALSE	FALSE

Identical answers. Both forms match the literal dot and both reject abc.

For a pattern with one backslash in it the doubling is survivable. For anything with several, the dollar-quoted form is much easier to read and much harder to get wrong.

Let's try the same thing on the real email column

The escaped and unescaped patterns were also run against the real addresses, expecting a visible difference. They did not produce one, and the grid stays in.

Query

SELECT EMAIL,
       REGEXP_LIKE(EMAIL, '.*\\.com')  AS ESCAPED,
       REGEXP_LIKE(EMAIL, '.*.com')    AS UNESCAPED_MATCHES_MORE_THAN_YOU_THINK
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 3 columns

EMAIL	ESCAPED	UNESCAPED_MATCHES_MORE_THAN_YOU_THINK
aisha.khan@example.com	TRUE	TRUE
marcus.reid@example.com	TRUE	TRUE
lena.fischer@example.com	TRUE	TRUE
tomas.novak@example.com	TRUE	TRUE
priya.raman@example.com	TRUE	TRUE
daniel.oconnor@example.com	TRUE	TRUE
sofia.marino@example.com	TRUE	TRUE
grace.lin@example.com	TRUE	TRUE

TRUE and TRUE on all eight rows. That is correct, not broken.

Every one of these addresses really does have a dot immediately before com. So the escaped pattern matches because the dot is there, and the unescaped one matches because its wildcard lands on that same dot. Both are right, for different reasons, and the column cannot tell them apart.

The made-up grid with a.c and abc in it is the one that proves the difference, because it contains the abc row that this column does not have. A test only distinguishes two patterns if the data contains a case where they disagree. Which is an argument for testing a regular expression against values you invent, not only against the table you happen to have.

Now let's count digits three ways

There are three spellings of “a digit” and they are all affected by the backslash rule, so let's put them in one grid and check they agree. We will add a count of everything that is not a digit at the end.

Query

SELECT PHONE_TXT,
       REGEXP_COUNT(PHONE_TXT, '[0-9]')   AS DIGITS_BRACKET_CLASS,
       REGEXP_COUNT(PHONE_TXT, '\\d')     AS DIGITS_BACKSLASH_D,
       REGEXP_COUNT(PHONE_TXT, $$\d$$)    AS DIGITS_DOLLAR_QUOTED,
       REGEXP_COUNT(PHONE_TXT, '[^0-9]')  AS NON_DIGITS
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

PHONE_TXT	DIGITS_BRACKET_CLASS	DIGITS_BACKSLASH_D	DIGITS_DOLLAR_QUOTED	NON_DIGITS
(704) 555-0142	10	10	10	4
704-555-0198	10	10	10	2
+1 416 555 0117	11	11	11	4
604.555.0163	10	10	10	2
+41 44 555 0188	11	11	11	4
919-555-0124	10	10	10	2
(617) 555-0179	10	10	10	4
+44 161 555 0133	12	12	12	4

[0-9], the doubled \\d and the dollar-quoted \d return the same count on every row. That is the point of putting them in one grid.

Now read the counts down the column and the data describes itself. Five of the numbers hold ten digits, two hold eleven and one holds twelve. The longer ones are the three rows whose raw value starts with a + and carries a country code.

NON_DIGITS inverts the class with a leading ^ inside the brackets. That is the punctuation, and it is a quick way to find out how messy a column is before you decide how to clean it.

Two meanings of ^

^ means two different things in this post. At the start of a pattern it anchors to the beginning of the value. At the start of a character class, inside [ ], it negates the class. Same character, different job, decided by where you put it.

Now let's do some real work with three date formats

DATE_TXT holds three formats. Before writing anything clever, let's count the separators. It is the cheapest way to find out how many shapes a text column actually has.

Query

SELECT ROW_ID, DATE_TXT,
       REGEXP_COUNT(DATE_TXT, '-')   AS DASHES,
       REGEXP_COUNT(DATE_TXT, '/')   AS SLASHES,
       REGEXP_COUNT(DATE_TXT, '[0-9]') AS DIGITS
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	DATE_TXT	DASHES	SLASHES	DIGITS
1	2023-01-12	2	0	8
2	01/28/2023	0	2	8
3	14-Feb-2023	2	0	6
4	2023-03-09	2	0	8
5	2023-04-05	2	0	8
6	05/21/2023	0	2	8
7	14-Aug-2023	2	0	6
8	2024-09-29	2	0	8

Three distinct signatures fall straight out. Two hyphens with eight digits is ISO. Two slashes is the US format. And two hyphens with only six digits is the one with a month name in the middle, because Feb and Aug contribute no digits at all.

Now let's turn those signatures into a classification, with REGEXP_LIKE inside a CASE expression. Here the implicit anchoring is working for us, because a date format is precisely the kind of claim that should describe the whole value.

Query

SELECT CASE WHEN REGEXP_LIKE(DATE_TXT, '[0-9]{4}-[0-9]{2}-[0-9]{2}') THEN 'ISO yyyy-mm-dd'
            WHEN REGEXP_LIKE(DATE_TXT, '[0-9]{2}/[0-9]{2}/[0-9]{4}') THEN 'US mm/dd/yyyy'
            WHEN REGEXP_LIKE(DATE_TXT, '[0-9]{2}-[A-Za-z]{3}-[0-9]{4}') THEN 'dd-Mon-yyyy'
            ELSE 'unrecognised'
       END AS FORMAT,
       COUNT(*) AS N
FROM SALES_RAW
GROUP BY 1
ORDER BY N DESC;

Result3 rows · 2 columns

FORMAT	N
ISO yyyy-mm-dd	4
US mm/dd/yyyy	2
dd-Mon-yyyy	2

Four ISO rows, two US and two day-month-year. That accounts for all eight rows of the table, but only if you trust the arithmetic. A classification is exactly the kind of query where a row can fall into the unrecognised bucket without anyone noticing.

So let's prove that none of them did.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(REGEXP_LIKE(DATE_TXT, '[0-9]{4}-[0-9]{2}-[0-9]{2}')
             OR REGEXP_LIKE(DATE_TXT, '[0-9]{2}/[0-9]{2}/[0-9]{4}')
             OR REGEXP_LIKE(DATE_TXT, '[0-9]{2}-[A-Za-z]{3}-[0-9]{4}')) AS RECOGNISED
FROM SALES_RAW;

Result1 row · 2 columns

ROWS_TOTAL	RECOGNISED
8	8

Eight rows, eight recognised. Nothing landed in the ELSE branch. That is the check worth writing every time you classify anything. The GROUP BY above shows you what it found, and this one shows you that it found something for every row.

The next step, elsewhere

Classifying a date format is not the same as converting it. Turning these three shapes into real DATE values is TRY_TO_DATE with a format string, which is Episode 75.

Can one pattern tell us an address is shaped like an address?

Episode 66 took an email address apart with POSITION and SUBSTR, and Episode 67 did it with SPLIT_PART. Neither of them could say whether the address was shaped like an address. One regular expression can.

Query

SELECT EMAIL,
       REGEXP_LIKE(EMAIL, '[a-z.]+@[a-z]+\\.[a-z]{2,}') AS LOOKS_LIKE_AN_EMAIL
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 2 columns

EMAIL	LOOKS_LIKE_AN_EMAIL
aisha.khan@example.com	TRUE
marcus.reid@example.com	TRUE
lena.fischer@example.com	TRUE
tomas.novak@example.com	TRUE
priya.raman@example.com	TRUE
daniel.oconnor@example.com	TRUE
sofia.marino@example.com	TRUE
grace.lin@example.com	TRUE

Read the pattern as a sentence: letters and dots, an @, letters, a literal dot, then at least two letters. The {2,} is a quantifier with no upper bound, and the \\. is the escaped dot from earlier. Without the backslashes it would accept any character there.

And as a count, so we are not reading a column of TRUE.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(REGEXP_LIKE(EMAIL, '[a-z.]+@[a-z]+\\.[a-z]{2,}')) AS VALID
FROM EMPLOYEES;

Result1 row · 2 columns

ROWS_TOTAL	VALID
8	8

Eight of eight valid. And this is the case where the anchoring rule is a gift rather than a nuisance. Because the pattern is anchored, it is asserting that the whole value is an address, which is what validation means. The same pattern in an unanchored engine would happily pass nonsense aisha.khan@example.com nonsense.

What this does and does not prove

This pattern is a shape check, not a correctness check. It only accepts lower-case letters, so a real address with a digit or a capital in it would be rejected. That is fine for demo data and wrong for a sign-up form. Validating email addresses properly with one regular expression is a famously bad idea. Validating that a column has the shape you expect is a very good one.

One last thing: what does Snowflake do with an invalid pattern?

Worth knowing before you build a pattern out of user input or a configuration table. Let's hand it something that is not a pattern at all, an unclosed character class, and see whether it errors or quietly matches nothing.

Query — expected to fail

SELECT REGEXP_LIKE('abc', '[a-') AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100048 (2201B): Invalid regular expression: '[a-', invalid UTF-8

The statement is refused, and the message names the offending pattern. That is the good outcome. A malformed pattern that silently matched nothing would give you an empty result set and no reason for it, and you would be looking at your data instead of at your pattern.

It also means a pattern assembled at run time can fail your statement. So if yours comes from a column or a parameter rather than from the query text, that is a failure mode to plan for rather than discover.

Key points

• REGEXP_LIKE is implicitly anchored at both ends. The documentation says so: '' becomes ^$ and ABC becomes ^ABC$. Your pattern must describe the whole value.
• So REGEXP_LIKE is not a contains test. It is an equals test that understands patterns. The bare word example matched none of the eight addresses that contain it.
• For a genuine contains test, wrap the pattern in .* on both sides, or just use LIKE.
• REGEXP_COUNT is not anchored. The same pattern on the same column returns FALSE from REGEXP_LIKE and four from REGEXP_COUNT. Same family, opposite default.
• Matches do not overlap: REGEXP_COUNT('aaaa', 'aa') is two, not three.
• Patterns are case sensitive by default. 'i' as the parameters argument turns that off, and on this data it doubled the number of matching rows.
• A backslash has to survive the SQL string literal before the regex engine sees it, so a literal dot is '\\.' in ordinary quotes, or a single \. inside a $$...$$ literal. The dollar-quoted form is the better choice for anything with more than one backslash.
• ^ anchors at the start of a pattern and negates inside [ ]. Two jobs, one character.
• REGEXP_LIKE in a CASE expression classifies text shapes well, and the implicit anchoring helps there. Always run the companion query that proves no row fell into the ELSE branch.
• An invalid pattern raises an error rather than matching nothing, which is the behaviour you want, and a reason to be careful with patterns built at run time.

Next in this series

In the next post we will carry on with regular expressions, but instead of only testing we will extract and rewrite. REGEXP_SUBSTR, REGEXP_REPLACE and REGEXP_INSTR, including capture groups and what the anchoring rule does and does not do to them.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the error pane and the comparison that failed to show a difference. The EMPLOYEES and SALES_RAW data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. The anchoring rule and the quoted sentence come from the Snowflake REGEXP_LIKE documentation.