REGEXP_LIKE in SQL Server 2025: Validating Codes, Emails and Phone Numbers

The product code looks close enough until an import accepts the wrong shape. REGEXP_LIKE lets SQL Server 2025 check a defined text pattern directly. Use it to reject malformed input while keeping format validation separate from business verification.

Set the Version and Compatibility Requirements

These examples require SQL Server 2025 and database compatibility level 170. The engine version and the database setting are separate checks. An upgraded engine can still host a database at an earlier level. REGEXP_LIKE is unavailable below the required level, so inspect the target before diagnosing a pattern that never reaches the matching engine.

SELECT SERVERPROPERTY('ProductVersion') AS EngineVersion;
SELECT name,compatibility_level FROM sys.databases WHERE database_id=DB_ID();

Use a disposable database already configured at level 170 for the demonstrations. Change production compatibility only through a reviewed plan with a query performance baseline. I check that setting before simplifying a pattern. An unknown function error says nothing about whether your expression correctly recognizes a product code. Keep setup failures and data validation failures distinct.

Match a Complete Product Code With REGEXP_LIKE

Suppose the approved code has two uppercase ASCII letters, a hyphen, and four digits. Anchors require the entire value to follow that shape. Without anchors, matching a valid substring inside extra text would still count as success. Character classes define the alphabet explicitly, and the repetition count states how many positions belong to each part.

CREATE TABLE #CodeInput(InputID int NOT NULL,ProductCode varchar(30) NULL);
INSERT #CodeInput VALUES
    (1,'AB-1234'),(2,'ab-1234'),(3,'AB-123'),(4,'extra AB-1234'),(5,NULL);
SELECT InputID,ProductCode
FROM #CodeInput
WHERE REGEXP_LIKE(ProductCode,'^[A-Z]{2}-[0-9]{4}$','c');
SELECT InputID,ProductCode
FROM #CodeInput
WHERE ProductCode IS NULL
   OR NOT REGEXP_LIKE(ProductCode,'^[A-Z]{2}-[0-9]{4}$','c');

The explicit c flag makes the case rule visible. The exception query handles NULL separately because a missing value needs its own policy. Run these temporary examples in one session. Keep raw input for rejection reports rather than silently editing it. A validator that repairs and accepts a code without telling anyone has become a transformation with an undocumented rule.

Compare REGEXP_LIKE With a Plain LIKE Pattern

LIKE supports bracket classes but has no repetition quantifier. Spell out the positions and use a binary collation for an ASCII capital rule. A byte length check excludes padded or extra input for this varchar contract. The regular expression is shorter because it expresses repetition directly. Neither version verifies that the code exists in a product catalog.

SELECT InputID,ProductCode
FROM #CodeInput
WHERE ProductCode COLLATE Latin1_General_100_BIN2
      LIKE '[A-Z][A-Z]-[0-9][0-9][0-9][0-9]'
  AND DATALENGTH(ProductCode)=7;
SELECT InputID,ProductCode
FROM #CodeInput
WHERE REGEXP_LIKE(ProductCode,'^[A-Z]{2}-[0-9]{4}$','i');

The i flag accepts case variations for the second query. Decide whether that means the stored value should remain as supplied or be normalized to uppercase. Validation and normalization should agree with uniqueness and lookup rules. I avoid letting a case insensitive match quietly redefine an identifier used elsewhere under a case sensitive comparison.

Put a REGEXP_LIKE Pattern in a CHECK Constraint

A CHECK constraint enforces the format for every ordinary insert and update. Pair it with NOT NULL when the field is required. A pattern alone does not define how unknown input should be handled. The sample table uses the same reviewed product rule, so application and database validation can be tested against one shared set of examples.

CREATE TABLE #ValidatedProduct
(
    ProductID int NOT NULL PRIMARY KEY,
    ProductCode varchar(7) NOT NULL,
    CHECK(REGEXP_LIKE(ProductCode,'^[A-Z]{2}-[0-9]{4}$','c'))
);
INSERT #ValidatedProduct(ProductID,ProductCode) VALUES(1,'AB-1234');
BEGIN TRY
    INSERT #ValidatedProduct(ProductID,ProductCode) VALUES(2,'A1-1234');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Validate existing rows before adding a constraint to a real table. Review bulk load and privileged maintenance paths too. Keep the pattern readable and its rule documented. A CHECK expression that nobody understands is difficult to change safely. The database will enforce the typo with admirable consistency if the pattern itself is wrong.

Keep Email Validation Modest

A practical shape check can require nonspace text on each side of an at sign and a dot in the domain portion. It rejects ordinary typing mistakes without claiming to implement every permitted email address syntax. Define the accepted contract for your application, including international addresses and any supported quoted local parts, before enforcing a simplified rule.

SELECT EmailText,
       CASE WHEN REGEXP_LIKE(EmailText,'^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$')
            THEN N'Accepted shape' ELSE N'Review shape' END AS ShapeStatus
FROM (VALUES(N'reader@example.test'),(N'broken address@example.test'),
            (N'no-at-sign'),(N'reader@example')) AS v(EmailText);

These are fictional address examples. Do not treat a match as evidence of delivery, ownership, or permission to send messages. Those requirements need separate application workflows. Which question are you asking: does the text fit your input contract, or can this person receive mail there? A text predicate can answer only the first question.

Allow Phone Separators Without Losing Structure

Phone input needs a country and numbering policy. This example accepts a North American ten digit shape, an optional country prefix of one, and selected separators. Its parentheses are paired through an alternative, rather than independently optional characters. It deliberately does not handle extensions or claim to cover every international number.

SELECT PhoneText
FROM (VALUES('2125550199'),('(212) 555-0199'),('+1 212-555-0199'),
            ('(212 555-0199'),('212-55-0199')) AS v(PhoneText)
WHERE REGEXP_LIKE(PhoneText,
    '^(\+?1[ .-]?)?(\([0-9]{3}\)|[0-9]{3})[ .-]?[0-9]{3}[ .-]?[0-9]{4}$');

Store the original and a normalized representation when the application needs both. Keep a country identifier with the normalized number. A successful match proves neither assignment nor reachability. Avoid stripping everything except digits and then assuming all remaining values share one country. That transformation loses context before the validation rule even starts.

Test Boundaries and Measure the Query

Create accepted and rejected examples for case, length, spaces, missing values, and unusual punctuation. Add values that contain a valid code inside extra text. Those test the anchors directly. Compare REGEXP_LIKE with a simpler predicate on representative data, using actual plans and IO statistics. The new function provides expressiveness, not a blanket promise of a faster access path.

For a large table, use a selective indexed predicate to narrow the candidate population when possible. Rechecking every historic row on every screen request wastes work when a trusted constraint already protects the field. Keep ingestion validation and investigative exception searches as distinct workloads. Save the rejected raw values with an owner for correction.

Change the Rule as a Data Contract

When product formats or contact rules change, version the accepted examples with the migration. Test old valid values as well as new ones. Decide whether old records remain valid or require conversion. Updating a constraint without that decision can make an unrelated row update fail because its unchanged field follows the earlier contract.

I review the exception query alongside the constraint. It explains what failed and keeps missing input visible. A regular expression is useful when its boundaries, case rules, and purpose remain clear. Format checks protect the shape of text, while catalog lookup and real world verification supply the meaning the pattern cannot establish.

Related reading on this blog: CHECK CONSTRAINT to Allow Only Digits in Column and Leading Wildcard LIKE Searches: Why They Scan and What Helps.

A format match is not business verification, it is one clear check on the input.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.