REGEXP_LIKE patterns

Concepts covered: Regex Matching

LIKE knows 2 wildcards, % for any run of characters and _ for exactly 1, and its pattern must cover the whole string. That handles prefix and suffix checks. It cannot ask for exactly 4 digits, and it has no way to express a letter-or-digit choice or 1 or more of something. Regular expressions can, and REGEXP_LIKE(text, pattern) returns TRUE when the pattern matches the text.

In this lesson's SQL a backslash inside a string literal reaches the regex engine as typed, so '\d' arrives as \d and means a digit.

One difference catches people coming from LIKE: REGEXP_LIKE matches anywhere in the string. REGEXP_LIKE('SUMMER25', '25') is TRUE because 25 appears somewhere inside it. To demand that a pattern covers the whole value, anchor it with ^ for the start and $ for the end.

The building blocks

Merchants type discount codes into a free-text field, so the 6 codes below follow no single format. The animation tests each against ^[A-Z]+\d+$: uppercase letters followed by digits, at least 1 of each, with nothing else in the string.

SELECT
  code_id,
  code
FROM discount_codes
WHERE REGEXP_LIKE(code, '^[A-Z]+\d+$')

Only SUMMER25 passes. summer25 fails at its first character, because [A-Z] covers uppercase only. FREESHIP has letters but no digit, and \d+ needs at least 1. VIP 10 has a space between its letters and digits that no part of the pattern allows, and both BF codes stop at their dashes.

Anchors decide what a pass means

Black Friday codes should look like BF-2024-40: the letters BF and a 4-digit year, then a 2-digit discount, with a dash between each part. The query below runs 2 rules side by side. LIKE 'BF-%' checks only the prefix, so it lets the 2-digit year in BF-24-40 through. The anchored pattern checks every character from start to end.

SELECT
  code,
  code LIKE 'BF-%' AS like_prefix,
  REGEXP_LIKE(code, '^BF-\d{4}-\d{2}$') AS strict_format
FROM discount_codes

Leave out the $ and the rule weakens without changing any of these rows: ^BF-\d{4}-\d{2} also accepts BF-2024-405, because the first 2 digits after the second dash satisfy \d{2} and nothing checks what follows. An unanchored pattern only checks that a matching piece exists somewhere in the value.

LIKE

• Must match the whole value
• 2 wildcards: % and _
• Can't count repetitions or name a set of characters
• Fine for fixed prefixes such as 'BF-%'

REGEXP_LIKE

• Matches anywhere unless anchored
• Supports character classes and quantifiers, plus anchors and alternatives
• Counts characters exactly with {n}
• Built for format rules such as ^BF-\d{4}-\d{2}$

Which discount codes does this filter keep?

SELECT
  code
FROM discount_codes
WHERE REGEXP_LIKE(code, '[A-Z]{4}')

• FREESHIP only, the code made of letters
• SUMMER25 and FREESHIP, which hold 4 capitals in a row
• SUMMER25, FREESHIP and VIP 10, all with capitals
• No codes, since none is exactly 4 letters long

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.