Regex operators and patterns

Concepts covered: Regex Matching

So far each value has been tested against a single pattern. A validation rule in a pipeline needs 4 more tools: alternatives and repeating groups, plus a way to take characters literally and a way to control letter case. Each card below pairs one of them with an input it accepts or rejects.

From one pattern to a rule set

A large pattern for an email address tells you that a value failed and nothing about why. Splitting the check into small rules inside a CASE expression gives every failing row a reason someone can act on. CASE tests its WHEN branches in order, so the first rule that fires names the problem.

The data is 1 contact email for each of the same 5 stores, the addresses the Black Friday email would go to.

SELECT
  store_id,
  email,
  CASE
    WHEN REGEXP_LIKE(email, '\s') THEN 'contains a space'
    WHEN NOT REGEXP_LIKE(email, '(?i)@[a-z0-9-]+(\.[a-z]+)+$') THEN 'missing domain suffix'
    WHEN REGEXP_LIKE(email, '[A-Z]') THEN 'needs lowercasing'
    ELSE 'valid'
  END AS email_issue
FROM store_contacts

tidal tees@shop.io stops at the first WHEN. hello@fernstudio has no dot after its @, so the group (\.[a-z]+)+ has nothing to repeat. Pixel.Prints@gmail.com passes the domain rule because that rule carries (?i). The uppercase rule lacks it, so the address trips there. That label marks cleanup work rather than a delivery failure: the address still receives mail, and the row goes to a normalization step. Stores 1 and 3 fall through to ELSE.

Capstone: who gets the Black Friday email

Now the whole lesson in a single query. Marketing wants stores that have never sold anything and have an address that will deliver. That is a NOT EXISTS against orders, joined by AND to an anchored pattern. The pattern accepts uppercase, since domain names ignore case and most mail providers also ignore it in the part before the @.

SELECT
  c.store_id,
  c.email
FROM store_contacts c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.store_id = c.store_id
)
  AND REGEXP_LIKE(c.email, '(?i)^[a-z0-9._]+@[a-z0-9-]+(\.[a-z]+)+$')

Stores 2 and 4 pass the NOT EXISTS. hello@fernstudio fails the pattern, so only Pixel Prints gets the email. Inside a character class, . is literal wherever it sits and needs no backslash, which is why [a-z0-9._] accepts the dot in Pixel.Prints. A - between 2 characters forms a range, as in a-z, so it is literal only as the first or last character of the class, which is why [a-z0-9-] puts it last.

Remove (?i) from the capstone's pattern and change nothing else. What does the query return?

SELECT
  c.store_id,
  c.email
FROM store_contacts c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.store_id = c.store_id
)
  AND REGEXP_LIKE(c.email, '^[a-z0-9._]+@[a-z0-9-]+(\.[a-z]+)+$')

• Store 4 still, because gmail.com is lowercase
• Stores 2 and 4, because NOT EXISTS is unchanged
• No rows, because the P in Pixel now fails
• An error, because the email column holds capitals

About this lesson

This section is part of the Filtering: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.