Using SQL Regex to Classify and Filter Messy Data

Messy ratings, jumbled addresses, and buried keywords: SQL regex filters and classifies all of it inside the query, in seven real interview questions.

Text data rarely arrives clean. A stars column stores 4 in one row and four in the next, an address reads Pier 39 here and 39 Pier there, and a free-text field hides the word bull inside bullish. SQL regex (or regular expressions) is how we filter and classify that mess in the query, without exporting it to Python first.

A regular expression in SQL matches a value against a pattern instead of a fixed string. With it, we can keep only rows whose values are all digits, extract a year from a wine title, or count how often a specific word appears across thousands of documents.

We pulled seven interview-style questions from StrataScratch's coding platform to show SQL regex working on real messy data. The examples run on PostgreSQL, and we flag where the syntax changes on MySQL, SQL Server, and Oracle. For every question, we show the table, what one output row means, the solution built up step by step, and the output.

What Is SQL Regex?

A regular expression describes a text pattern: one or more digits, a word with a boundary on each side, an optional prefix, a set of allowed characters. SQL regex is that pattern language wired into the database so you can match, extract, replace, and split text in a query.

In PostgreSQL, the core pieces are small:

• ~ returns true when a value matches a pattern. !~ is the negated version. ~* and !~* are the case-insensitive forms.
• regexp_matches(text, pattern, 'g') returns the matches, one row per match, when the g (global) flag is set.
• regexp_replace(text, pattern, replacement, 'g') rewrites every match.
• regexp_split_to_table(text, pattern) splits a string into rows.

The patterns themselves use a shared vocabulary. 

[0-9] is a character class (any digit). ^ and $ anchor a match to the start and end of the value. + and * are quantifiers (one-or-more, zero-or-more). (plum|cherry) is alternation (this word or that one). \D matches any non-digit. In PostgreSQL, \m and \M mark the start and end of a word, and [[:punct:]] is a POSIX class for punctuation.

A single predicate shows how they combine. stars ~ '^[0-9]+$' reads as: from start (^) to end ($), the value is one or more digits ([0-9]+). Anything with a letter, a space, or a decimal point fails. That one line is the difference between a clean cast and a query that errors on the first four.

Why LIKE Breaks Down

LIKE and ILIKE match a substring with % wildcards. contents ILIKE '%optimism%' is true when optimism appears anywhere in the value, and that same simplicity is where it starts to leak on three fronts:

• False positives: %...% has no idea where a word starts or ends, so %optimism% matches optimism, optimismo, and optimism-driven all the same. If the requirement is the exact word, LIKE cannot tell them apart.
• Brittle patterns: ruling out each lookalike means adding another NOT LIKE condition for every near-miss you happen to spot, and the pattern still misses the one you did not think of.
• Unreadable logic: a predicate that has grown three or four NOT ILIKE exceptions buries the actual intent of the query under a pile of special cases.

On a table where no value contains a lookalike substring, a loose match returns the same rows as a stricter one would, and the question below is a case like that. The moment a value can contain a near-miss, or the requirement says "the word" instead of "mentions," a regex word boundary is the safer choice.

Keyword Filtering: LIKE vs SQL Regex

Data View

google_file_store holds one row per stored file: a filename and the file's contents as free text.

Grain (what one output row means): one file whose name starts with draft and whose contents mention optimism.

Common Mistakes

contents ILIKE '%optimism%' is a substring match: it cannot tell optimism apart from optimismo or optimism-driven, because the substring is present either way. None of the stored files here contain a lookalike, so the loose match returns the same rows as a stricter one would. 

A different table might not be so forgiving; once a lookalike substring is possible, LOWER(contents) ~ '\moptimism\M' is the safer pattern, anchoring the match to a whole word with \m and \M. The filename ILIKE 'draft%' half is a fair use of LIKE either way: an anchored prefix, which regex would write as ^draft.

For a deeper look at what LIKE and wildcards can and can't do on their own, see our guide to SQL LIKE Queries for Pattern Matching.

Solution

1) Keep draft files that mention the keyword 

Output

Same table, higher stakes next. The next question counts exact word occurrences in the same contents column, where a substring match cannot get away with the shortcut it took here.

SQL Regex for Exact Word Matching

Data View

Same google_file_store table. We count how many times the exact words bull and bear appear in contents, case-insensitively, counting every occurrence even when a word repeats in one row, and excluding substrings like bullish or bearing.

Grain (what one output row means): one word (bull or bear) and its total number of occurrences across all files.

How This Shows Up In Interviews

The question hands you the failure mode: "exclude substrings like bullish or bearing." That is a direct request for word boundaries, and \m(bull)\M supplies them. A follow-up we have seen is "count every occurrence, not every row." A boolean ILIKE can only tell you whether a row contains the word. regexp_matches(..., 'g') returns one row per match, so a LATERAL join over it followed by COUNT(*) counts repeats inside the same document too.

Solution

1) Count one word with word boundaries

We match bull with a start-of-word (\m) and end-of-word (\M) escape so bullish does not count. The g flag makes regexp_matches emit a row per hit, and the LATERAL join lets us count those rows.

SELECT 'bull' AS word,
       COUNT(*) AS nentry
FROM google_file_store,
     LATERAL regexp_matches(LOWER(contents), '\m(bull)\M', 'g');

2) Stack both word counts (final solution)

Output

Same table as the LIKE query, same word, different result. That is the whole case for regex over LIKE when a word boundary matters.

When SQL Regex Is the Right Tool

Reach for regex when the pattern needs more than a fixed substring:

• A character class or shape, like "all digits" or "a letter followed by four numbers."
• A word boundary, so rose does not fire inside rosemary.
• Alternation, matching any of several terms in one predicate.
• Extraction, replacement, or splitting driven by a pattern rather than a fixed position.

Stay with plain LIKE for a simple prefix, suffix, or substring: filename ILIKE 'draft%' is clearer and can use an index. Stay with string functions like split_part or substring when the position is fixed and known. And when you find yourself parsing the same field the same way in query after query, the right fix is often a modeled column, which we cover under alternatives.

How to Filter Data with SQL Regex

Filtering is the smallest, most common regex job: keep the rows whose values match a shape, drop the rest. The cleanest example is guarding a cast. A column typed as text can hold 4, four, or an empty string, and :: INTEGER throws on anything that is not a clean number. A regex predicate removes the bad rows before the cast runs.

SQL Regex for Validating Numeric Data

Data View

yelp_reviews holds one row per review. The stars column is stored as text and contains both numeric values and non-numeric junk, so a direct cast is unsafe.

Grain (what one output row means): one review whose stars value is entirely digits, with stars returned as an integer.

Validation Checks

Before trusting the cast, confirm the pattern isolates the rows you expect. Run SELECT stars, stars ~ '^[0-9]+$' FROM yelp_reviews and eyeball which values return true. The anchors matter: [0-9]+ without ^ and $ would match 4 stars because it finds digits somewhere inside the value. Anchoring to the whole string is what makes ~ '^[0-9]+$' a safeguard. Note it also rejects decimals like 4.5, since . is not in the class. If half-star ratings exist, the pattern needs to change.

Solution

1) Return all columns for rows where stars is all digits (final solution)

Output

The pattern ^[0-9]+$ is the workhorse of numeric filtering. It answers "is this value a plain integer?" and nothing else. We come back to it in the FAQs, and again in the classify section, where the same test decides which token in an address is the house number.

How to Classify Messy Data with SQL Regex

Filtering keeps or drops a row. Classifying labels it. Regex classifies by testing each value against a pattern and using the result to route the row, usually inside a CASE. The address problem below is the clearest example, and it shows regex doing one job while split_part and CASE handle the rest. If CASE syntax itself is rusty, our full guide to CASE WHEN statements covers the basics before you get to the address example below 

SQL Regex for Classifying Inconsistent Data

Data View

sf_restaurant_health_violations holds one row per recorded health violation, including the business's business_postal_code and business_address. Addresses are inconsistent: some read 1000 Folsom St, others Pier 39, others 39 Pier.

Grain (what one output row means): one postal code and the number of distinct street names in it.

Trade-offs

The task splits into three jobs, and each tool does the one it is best at. split_part(business_address, ' ', 1) extracts a token by position. ~ '^[0-9]+$' classifies that token as numeric or not. CASE encodes the decision: if token 1 is the number, the street is token 2; if token 2 is the number (the 39 Pier case), the street is token 1; otherwise take token 1. Trying to do all three with regex alone would be harder to read and slower. Splitting the work is the senior move here.

Edge Cases

Two assumptions can break this. First, NULL postal codes: the WHERE business_postal_code IS NOT NULL guard drops them before they reach the GROUP BY, so a NULL code does not become its own bucket. Second, the "first word" rule assumes the street name is a single token, so Van Ness collapses to van. That is acceptable for a rough street count and would be wrong for exact address matching. Naming that assumption out loud is what an interviewer listens for.

Solution

1) Classify each address and extract the street token

For each row, we test which token is the house number and take the other one as the street name, lower-cased so Folsom and folsom collapse.

SELECT
    business_postal_code,
    business_address,
    LOWER(
        CASE
            WHEN split_part(business_address, ' ', 1) ~ '^[0-9]+$' THEN split_part(business_address, ' ', 2)
            WHEN split_part(business_address, ' ', 2) ~ '^[0-9]+$' THEN split_part(business_address, ' ', 1)
            ELSE split_part(business_address, ' ', 1)
        END
    ) AS street_name
FROM sf_restaurant_health_violations
WHERE business_postal_code IS NOT NULL;

2) Count distinct streets per postal code (final solution)

Output

SQL Regex Correctness Pitfalls

Regex is precise about what you write, which means most bugs are patterns that match slightly more or slightly less than you meant. Two traps show up constantly: accidental substring hits, and the empty string that regex returns when nothing matches.

SQL Regex for Handling Word Variations

Data View

winemag_p1 holds one row per wine review, including the winery and a free-text description of the wine's aromas. We want wineries whose descriptions mention plum, cherry, rose, or hazelnut in the singular, and not the plural forms.

Grain (what one output row means): one distinct winery matching the aroma rule.

Common Mistakes

Three mistakes hide in this one query. 

First, no boundaries: description ~ 'rose' matches rosemary and prosecco. The escapes \m and \M pin the match to a whole word. 

Second, treating "match the singular" as enough. Matching cherry does not exclude cherries, so the second predicate uses !~ to reject the plurals explicitly. 

Third, case. Rather than the case-insensitive operator ~*, this solution first lowercases the column with lower(description) and matches against lowercase patterns, keeping the two predicates consistent. Either approach works; mixing them is where people trip.

Solution

1) Match any of the singular aromas as whole words

Alternation (plum|cherry|rose|hazelnut) matches any one of the four, and \m ... \M keeps each to a whole word.

SELECT DISTINCT winery
FROM winemag_p1
WHERE lower(description) ~ '\m(plum|cherry|rose|hazelnut)\M';

2) Exclude the plural forms (final solution)

Output

The second pitfall is about what a regex returns when it finds nothing. This next question extracts a year and then has to survive titles that contain no year at all.

SQL Regex for Extracting Numbers from Text

Data View

winemag_p2 holds one row per wine review, including the country and a title that usually contains the vintage year, as in Château 2016 Vranec. We want the year, as a numeric type, for wines from Macedonia.

Grain (what one output row means): one Macedonian wine with its extracted vintage year.

Edge Cases

regexp_replace(title, '\D', '', 'g') deletes every non-digit, collapsing the title down to its digits. The sharp edge: if a title has no digits, the result is not NULL, it is the empty string '', and ''::NUMERIC throws an error that kills the whole query. This is the single most common regex-extraction bug. Regex functions signal "no match" with an empty string, never with SQL NULL, so you have to convert it yourself. NULLIF(..., '') turns the empty result into NULL, the cast becomes safe, and the row survives with year = NULL instead of crashing the query.

Solution

1) Strip every non-digit from the title

SELECT
    title,
    regexp_replace(title, '\D', '', 'g') AS digits
FROM
    winemag_p2
WHERE
    country = 'Macedonia';

2) Convert the empty case to NULL, then cast (final solution)

Output

How to Test SQL Regex Patterns

A regex predicate either matches or it does not, and it will not tell you why. Test patterns before you trust them.

The fastest check is to project the match result next to the value instead of filtering on it. SELECT stars, stars ~ '^[0-9]+$' AS is_int FROM yelp_reviews shows you which rows pass and which fail, so you can spot a value the pattern wrongly keeps or drops. Run it on a sample first with a LIMIT.

For extraction and matching, regexp_matches(..., 'g') is its own test tool: it returns exactly what matched, one row per hit. We used it in the bull/bear count to see every occurrence, and it works the same way for debugging, when you want proof that \m(rose)\M is not firing on rosemary. For splitting, regexp_split_to_table shows the tokens your split actually produced, which is how you catch an empty token from a double space or a stray delimiter.

Two checks catch most bugs: compare the count of matched rows against the count of unmatched rows and confirm the split makes sense, and spot-check the boundary cases by hand (a value with no match, a value with a repeat, a NULL).

SQL Regex Performance Trade-Offs

Regex is evaluated per row, and it does not use a standard B-tree index the way an anchored LIKE 'abc%' can. On a few thousand rows, this never matters. On tens of millions, it does.

Two costs are worth naming. First, a regex in a WHERE clause scans every row and runs the engine on each value. If a cheaper condition can shrink the set first, put it before the regex, the way the aroma query already filters on lowercase and the street query guards on IS NOT NULL. Second, functions that produce rows multiply the work: regexp_split_to_table in the word-frequency query turns each document into one row per word, and regexp_matches(..., 'g') in the bull/bear count does the same per match. A table with long text fields can explode into a very large intermediate result.

When a regex filter runs constantly on a large table, the durable fix is to stop parsing at query time. Compute the parsed value once and store it, either as a generated column or during load, and index that. We return to this under alternatives.

Alternatives to SQL Regex

Regex is not always the cheapest or clearest tool. Before reaching for it, weigh the alternatives.

Plain LIKE and ILIKE win for a fixed prefix, suffix, or substring. filename ILIKE 'draft%' is readable and index-friendly, and no regex improves on it.

String functions win when the position is known. split_part, substring, left, right, position, and translate handle fixed-shape parsing without a pattern engine. The street query leans on split_part for exactly this reason and uses regex only for the numeric test.

Full-text search wins for real-word search at scale. PostgreSQL's tsvector and tsquery are built for matching words across large document sets with stemming and an index, which is a better fit than a table scan of regexp_matches when search is a core feature rather than a one-off.

Data modeling wins when you parse the same field the same way repeatedly. If every query strips a year out of a title, the year belongs in its own column, populated once, indexed, and validated on write. That turns a per-query regex into a one-time cost.

Preprocessing wins when the same cleanup runs on every read. The word-frequency query strips punctuation with regexp_replace(t.contents, '[[:punct:]]', '', 'g') every time it runs, and every other query against contents would redo that same work. Clean the text once, when the row is loaded, and later queries read already-clean text instead of paying for the regex again.

Derived columns win when the same extraction runs on every read. If every query pulls a year out of a wine title with regexp_replace and a NULLIF guard, the year belongs in its own column instead, computed once, either as a generated column or during load. Filtering on year = 2016 is also cheaper than re-evaluating NULLIF(regexp_replace(title, '\D', '', 'g'), '')::NUMERIC = 2016 on every row.

Normalization wins when one field packs more than one fact. business_address holding a house number and a street name jammed into one string is why the street query needs split_part and a regex test just to work out which token is which. Splitting that column into street_number and street_name at write time turns the three-tool question the street query answers into a plain GROUP BY on a column that already holds the right value.

Common SQL Regex Use Cases

Across the questions above, the same handful of jobs keep coming up:

• Validate: keep only values that match a shape, as ^[0-9]+$ does for the star ratings.
• Extract: pull a structured value out of free text, as \D stripping does for the vintage year.
• Clean and normalize: strip or standardize characters before further work.
• Mine text: count exact words across documents, as the bull/bear query does.
• Classify: label or route a row by which pattern it matches, as the address CASE does.

Cleaning and tokenizing show up so often that they deserve their own example. The next question stacks two regex tools to turn a text column into a word-frequency table.

SQL Regex for Cleaning and Tokenizing Text

Data View

The same google_file_store table. We count how many times each distinct word appears across all the contents values.

Grain (what one output row means): one distinct word and how many times it occurs in total.

Trade-offs

This is the "clean, then split" recipe. regexp_replace(t.contents, '[[:punct:]]', '', 'g') strips punctuation using the POSIX class [[:punct:]], so optimism, and optimism normalize to the same token. Then regexp_split_to_table(..., E'\\s+') splits the cleaned text on runs of whitespace into one row per word. The E'\\s+' is an escape-string literal: the E prefix lets \\s mean "whitespace," and + collapses multiple spaces into a single split so you do not get empty tokens. Doing the same cleanup with nested replace() calls would be longer and would still miss cases the character class covers for free.

Solution

1) Clean punctuation and split into one row per word

SELECT t.filename,
       regexp_split_to_table(regexp_replace(t.contents, '[[:punct:]]', '', 'g'), E'\\s+') AS word
FROM google_file_store t;

2) Group and count each word (final solution)

Output

We cover regexp_split_to_table and its string-to-array cousins in more depth in our guide to string manipulation in SQL.

SQL Regex Syntax by Database

The patterns are mostly portable. The operators and functions around them are not. The ~ operator, the regexp_* functions, and the \m/\M word boundaries used above are PostgreSQL-specific. Here is how the common jobs translate.

Two notes matter in interviews. SQL Server had no native regex operator for years; teams worked around it with limited LIKE bracket classes or CLR functions. SQL Server 2025 added REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, and related functions, so newer patterns now match those in the table. And SQLite has a REGEXP operator only when the host application registers a regexp() function, so it is not available by default. StrataScratch runs PostgreSQL, which is why the solutions above use ~ and the regexp_* family.

LIKE vs Regex vs String Functions vs Data Modeling

Four tools, one decision. The honest answer is that each owns a range, and picking well is the skill. The decision rules follow from the table. If the pattern is a fixed substring, use LIKE.

If it is a shape or needs a boundary, use regex. If the position is fixed, string functions are clearer and faster. And if the same parse runs in query after query on a large table, model the value into its own column so you pay the cost once. A senior answer names the trade-off out loud: a regex in an ad hoc query is fine, but the same regex in a nightly job over a hundred million rows is a reason to add a parsed column.

SQL Regex Best Practices

• Anchor when you mean the whole value. ^[0-9]+$ checks the entire string; [0-9]+ alone matches digits anywhere inside it.
• Use word boundaries for whole-word matches. \m word \M stops rose from firing inside rosemary.
• Guard casts against the empty string. regexp_replace returns '', not NULL, when nothing matches, so wrap it in NULLIF(..., '') before casting.
• Decide case handling once. Either lowercase the column and match lowercase patterns, or use ~* throughout. Do not mix the two.

• Test with regexp_matches before filtering. Project the match result next to the value so you can see what passes and what fails.
• Filter cheaply first. Put an indexable or low-cost condition ahead of the regex to reduce the row set.
• Watch functions that produce rows. regexp_split_to_table and global regexp_matches multiply rows and can blow up an intermediate result.
• Know your dialect. ~ and regexp_* are PostgreSQL; MySQL, Oracle, and SQL Server 2025 use the REGEXP_* functions instead.

Conclusion

SQL regex earns its place on the jobs LIKE and string functions cannot do cleanly: matching a shape, requiring a word boundary, extracting a value from free text, or classifying a row by pattern. The seven questions here walk that range, from a one-line digit filter to a word-frequency table built out of regexp_replace and regexp_split_to_table.

The two habits that separate a working query from a fragile one are worth repeating: anchor and bound your patterns so they match exactly what you mean, and handle the empty string that regex hands back on no match. Get those right, test the pattern before you trust it, and keep an eye on cost at scale, and regex becomes a reliable way to filter and classify messy data without leaving the database.

FAQs

Is SQL Regex Slow?

It can be on large tables, because a regex predicate runs per row and cannot use a standard B-tree index the way an anchored LIKE 'abc%' can. On small and medium tables, the difference is negligible. When it matters, filter with a cheaper condition first, and if the same regex runs constantly, precompute the parsed value into an indexed column instead of matching at query time.

How Do I Match Only Numbers Using SQL Regex?

Anchor a digit class to the whole value: col ~ '^[0-9]+$' is true only when the value is one or more digits from start to end. To extract the digits from a mixed string instead of filtering, strip the non-digits with regexp_replace(col, '\D', '', 'g'). Note that ^[0-9]+$ rejects decimals and negatives, so widen the class if you need those.

How Do I Classify Rows Using Regex in SQL?

Put the regex test inside a CASE. Test each value against a pattern and use the result to assign a label or pick a branch, as the address query does: CASE WHEN split_part(addr,' ',1) ~ '^[0-9]+$' THEN ... END. The regex decides the category; the CASE records the decision.

How Do NULL Values Behave with Regex?

A regex match against NULL returns NULL, not true or false, so a row with a NULL value never passes a ~ filter. The other direction is the trap: regexp_replace and regexp_matches return an empty string, never NULL, when nothing matches. Wrap the result in NULLIF(..., '') if you need a real NULL before casting or aggregating.

Should I Use Regex to Validate Email Addresses in SQL?

For a rough check, yes: a pattern like col ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$' catches obviously malformed values. A fully correct email regex is enormous and still cannot confirm the address exists. Use a simple pattern for shape validation in the database, and verify deliverability with a confirmation email rather than a longer regex.

Can Regex Replace Data Normalization?

No. Regex cleans and reshapes text at read time, as the word-frequency query does when it strips punctuation and splits on whitespace. Normalization changes how the data is stored, so every query benefits and the cost is paid once. Use regex to prototype the cleanup and handle genuinely ad hoc text, and move repeated parsing into the schema when a field is read the same way over and over.