Enforcing Formats With a REGEXP_LIKE Check Constraint

A REGEXP_LIKE check constraint makes the database refuse product codes that do not match the shape you chose. It stops the typo at the door, which is a lot cheaper than finding it later in a report.

Why check the shape at the door

Someone types abc-1234 instead of ABC-1234. Nothing fails. The row goes in, and a month later a report quietly misses it, because the join to the product list never finds a lowercase code. Nobody gets an error. Somebody just gets a wrong number.

The rule in this post is simple: three uppercase letters, a hyphen, and four digits. I used SQL Server 2025, where REGEXP_LIKE is available. The demo creates one table named ProductCodeDemo and drops it at the end.

Write the constraint

The pattern starts with ^ and ends with $. Those anchors mean the whole value must match, not just a piece of it. The c flag asks for a case-sensitive match, so lowercase letters are rejected.

DROP TABLE IF EXISTS dbo.ProductCodeDemo;

CREATE TABLE dbo.ProductCodeDemo
(
    Id int PRIMARY KEY,
    ProductCode varchar(20) NOT NULL,
    CONSTRAINT CK_ProductCodeDemo_Code
        CHECK (REGEXP_LIKE(ProductCode, '^[A-Z]{3}-[0-9]{4}$', 'c'))
);

INSERT dbo.ProductCodeDemo VALUES (1, 'ABC-1234');

SELECT Id, ProductCode FROM dbo.ProductCodeDemo ORDER BY Id;

The valid code goes in without a fuss.

Test the rule with good and bad codes

A constraint you have not tried to break is a constraint you have not tested. The first query checks several codes against the pattern without inserting anything. The Unanchored column shows the same test with the ^ and $ removed.

SELECT Label,
       CASE WHEN REGEXP_LIKE(Code, '^[A-Z]{3}-[0-9]{4}$', 'c') THEN 1 ELSE 0 END AS Accepted,
       CASE WHEN REGEXP_LIKE(Code, '[A-Z]{3}-[0-9]{4}', 'c') THEN 1 ELSE 0 END AS Unanchored
FROM (VALUES ('Valid', 'ABC-1234'),
             ('Lowercase', 'abc-1234'),
             ('Prefix', 'XABC-1234'),
             ('Trailing space', 'ABC-1234 '),
             ('Too short', 'ABC-123'),
             ('No hyphen', 'ABC1234')) AS v(Label, Code)
ORDER BY Label;

Only Valid is accepted. Without the anchors, Prefix, Trailing space, and Valid all pass. That is the classic mistake: the unanchored pattern finds a good code inside a bad value and says yes.

Now try real inserts. Each one is caught, so you can see the error numbers.

BEGIN TRY
    INSERT dbo.ProductCodeDemo VALUES (2, 'abc-12');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

BEGIN TRY
    INSERT dbo.ProductCodeDemo VALUES (3, NULL);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

The bad shape fails with error 547, a CHECK conflict. The NULL fails with error 515, and that one comes from NOT NULL, not from the pattern.

NULL slips through a CHECK

That second error matters. A CHECK constraint rejects a row only when the condition is false. For a NULL the answer is unknown, and unknown passes. Here is a nullable column with the same rule.

DROP TABLE IF EXISTS #Loose;

CREATE TABLE #Loose (Code varchar(20) NULL CHECK (REGEXP_LIKE(Code, '^[A-Z]{3}-[0-9]{4}$', 'c')));

INSERT #Loose VALUES (NULL);

SELECT COUNT(*) AS NullRows FROM #Loose;

NullRows is 1. If a code is required, say so with NOT NULL. The two rules do different jobs.

Add the rule to a table that already has data

Real tables come with history. Before you add the rule, hunt for rows that already break it. Then try adding it WITH CHECK, which tests the existing rows too.

DROP TABLE IF EXISTS #LegacyCodes;

CREATE TABLE #LegacyCodes (Id int PRIMARY KEY, ProductCode varchar(20) NOT NULL);

INSERT #LegacyCodes VALUES (1, 'ABC-1234'), (2, 'xyz-99');

SELECT Id, ProductCode
FROM #LegacyCodes
WHERE NOT REGEXP_LIKE(ProductCode, '^[A-Z]{3}-[0-9]{4}$', 'c')
ORDER BY Id;

BEGIN TRY
    ALTER TABLE #LegacyCodes WITH CHECK ADD CONSTRAINT CK_Legacy
        CHECK (REGEXP_LIKE(ProductCode, '^[A-Z]{3}-[0-9]{4}$', 'c'));
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

Row 2 is the bad one, and the ALTER fails with error 547. The tempting shortcut is WITH NOCHECK, which skips the old rows. It works, but look at the catalog afterward.

ALTER TABLE #LegacyCodes WITH NOCHECK ADD CONSTRAINT CK_Legacy
    CHECK (REGEXP_LIKE(ProductCode, '^[A-Z]{3}-[0-9]{4}$', 'c'));

SELECT name, is_disabled, is_not_trusted
FROM tempdb.sys.check_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#LegacyCodes');

ALTER TABLE #LegacyCodes DROP CONSTRAINT CK_Legacy;

UPDATE #LegacyCodes SET ProductCode = 'XYZ-0099' WHERE Id = 2;

ALTER TABLE #LegacyCodes WITH CHECK ADD CONSTRAINT CK_Legacy
    CHECK (REGEXP_LIKE(ProductCode, '^[A-Z]{3}-[0-9]{4}$', 'c'));

SELECT name, is_disabled, is_not_trusted
FROM tempdb.sys.check_constraints
WHERE parent_object_id = OBJECT_ID('tempdb..#LegacyCodes');

The first result shows is_not_trusted as 1. The constraint is on, but SQL Server cannot rely on it, because old rows were never checked. After I fix the row and add it WITH CHECK, is_not_trusted is 0. Do it the honest way.

Shape is not meaning

A well-formed code can still be a duplicate or point to a product that does not exist. Use a unique key and a foreign key for that. Also keep a short list of good and bad codes next to the pattern, and rerun it whenever the pattern changes. A tiny edit to a regular expression can quietly change what the business accepts.

DROP TABLE IF EXISTS #LegacyCodes;
DROP TABLE IF EXISTS #Loose;
DROP TABLE IF EXISTS dbo.ProductCodeDemo;

Try to break your own constraint before your users do.

A format constraint is not business identity, it is a rule about the stored shape.

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