Launch Deal · 50% off 1st month ·

1.7 — Regular Expressions, Fuzzy String Matching and Graph Queries

DP-800 Domain 1 · Skill 1.7

What You Need to Know

Newer SQL platforms add native regular expressions and fuzzy matching, two big helps for cleansing text before it feeds search and AI. Graph tables add pattern matching across relationships.

Regular expression functions

Available in SQL Server 2025, Azure SQL Database, SQL database in Fabric and Azure SQL Managed Instance (2025 or always-up-to-date policy). The syntax is based on the RE2 library.

Function Returns Example use
REGEXP_LIKE(text, pattern [, flags]) Boolean (use it in WHERE or a CHECK constraint) Validate an email or postal code format
REGEXP_REPLACE(text, pattern, replacement ...) Modified string Strip non-digits from phone numbers
REGEXP_SUBSTR(text, pattern ...) The Nth matching substring Extract an order number from free text
REGEXP_INSTR(text, pattern ...) Start or end position of a match Find where a token starts
REGEXP_COUNT(text, pattern ...) Number of matches Count hashtags
REGEXP_MATCHES(text, pattern ...) Table of captured groups Pull out every key=value pair
REGEXP_SPLIT_TO_TABLE(text, pattern ...) Table of the pieces Split on any whitespace or punctuation
-- Enforce a SKU format: three capital letters, a dash, four digits
ALTER TABLE dbo.Product ADD CONSTRAINT CK_Sku CHECK (REGEXP_LIKE(Sku, '^[A-Z]{3}-\d{4}$'));

-- Normalize phone numbers to digits only
UPDATE dbo.Customer SET Phone = REGEXP_REPLACE(Phone, '[^0-9]', '');
  • The flags include 'i' for case-insensitive matching.
  • LIKE handles simple wildcards. Use regex for real patterns (alternation, repetition counts, character classes).
  • Input is capped at 2 MB for varchar(max) and nvarchar(max), and complex patterns cost CPU, so check the execution plan on large tables.

Fuzzy string matching (preview)

Function Returns Meaning
EDIT_DISTANCE(a, b) int Number of edits (insert, delete, substitute, transpose) to turn a into b (Damerau-Levenshtein)
EDIT_DISTANCE_SIMILARITY(a, b) int 0–100 100 means identical. "Colour" and "Color" score 83
JARO_WINKLER_DISTANCE(a, b) float Lower is more similar (0 means identical), and it favors a shared prefix
JARO_WINKLER_SIMILARITY(a, b) int 0–100 Higher is more similar
-- Likely duplicate customers whose names differ by a typo
SELECT a.CustomerID, b.CustomerID, a.Name, b.Name
FROM dbo.Customer a JOIN dbo.Customer b
  ON a.CustomerID < b.CustomerID AND a.PostalCode = b.PostalCode
WHERE EDIT_DISTANCE_SIMILARITY(a.Name, b.Name) >= 85;

Block first (join on a postal code or the first letter) so the comparison doesn't run on every possible pair. Use Jaro-Winkler for short strings such as names, where the start matters most.

Graph queries with MATCH

CREATE TABLE dbo.Person (PersonID int PRIMARY KEY, Name nvarchar(100)) AS NODE;
CREATE TABLE dbo.ReportsTo AS EDGE;

-- Who reports directly to Ana?
SELECT emp.Name
FROM dbo.Person emp, dbo.ReportsTo r, dbo.Person mgr
WHERE MATCH(emp-(r)->mgr) AND mgr.Name = 'Ana';

-- Everyone in Ana's chain, at any depth
SELECT p1.Name, STRING_AGG(p2.Name, '->') WITHIN GROUP (GRAPH PATH) AS Chain
FROM dbo.Person p1, dbo.ReportsTo FOR PATH r, dbo.Person FOR PATH p2
WHERE MATCH(SHORTEST_PATH(p1(-(r)->p2)+)) AND p1.Name = 'Ana';
  • Node tables hold entities, and edge tables hold relationships (with $from_id and $to_id). Edges can carry their own properties.
  • MATCH describes patterns with arrows: a-(e)->b, and chains such as a-(e1)->b-(e2)->c.
  • SHORTEST_PATH with + (or {1,n}) walks paths of any length, which in plain joins would need recursive CTEs.

Key Concept: REGEXP_LIKE validates, REGEXP_REPLACE cleans, and REGEXP_SUBSTR or REGEXP_MATCHES extract. Fuzzy functions find near-duplicates: for the similarity functions, higher means closer, and for Jaro-Winkler distance, lower means closer. MATCH with SHORTEST_PATH queries graphs.

Exam Traps

  • Reading JARO_WINKLER_DISTANCE as a similarity (0 is identical)
  • Comparing every row with every other with fuzzy functions, without blocking first
  • Using LIKE '%[0-9]%' tricks where a regex expresses the rule exactly
  • Expecting the regex table-valued functions in Fabric Data Warehouse (only the scalar ones are in preview there)

Check your understanding

Practice Scenario

Product codes must always have three uppercase letters, a dash and four digits (for example ABC-1234), whichever application inserts them. The database is Azure SQL Database. What is the most robust way to enforce this?

Report issue

Log in to save your progress.

Sources