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
flagsinclude'i'for case-insensitive matching. LIKEhandles simple wildcards. Use regex for real patterns (alternation, repetition counts, character classes).- Input is capped at 2 MB for
varchar(max)andnvarchar(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_idand$to_id). Edges can carry their own properties. MATCHdescribes patterns with arrows:a-(e)->b, and chains such asa-(e1)->b-(e2)->c.SHORTEST_PATHwith+(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_DISTANCEas 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?
Log in to save your progress.
Sources
- Regular expressions, Microsoft Learn
- EDIT_DISTANCE_SIMILARITY (Transact-SQL), Microsoft Learn
- JARO_WINKLER_DISTANCE (Transact-SQL), Microsoft Learn
- MATCH (SQL Graph), Microsoft Learn
- SHORTEST_PATH (SQL Graph), Microsoft Learn