Digits in a Column: A CHECK Constraint for Digits Only

To allow only digits in a column, add a CHECK constraint that rejects any character outside 0 to 9. The usual pattern needs two small repairs before it is safe. Without them, an empty string and a few look-alike digits get through.

The Usual Pattern

The demo creates a database named DigitsOnlyDemo and a table of postal codes. The first script builds the table without a constraint, so the later steps can add one.

IF DB_ID(N'DigitsOnlyDemo') IS NULL CREATE DATABASE DigitsOnlyDemo;
GO
USE DigitsOnlyDemo;
GO
DROP TABLE IF EXISTS dbo.PostalCodes, dbo.ZipCodes, dbo.Legacy;
CREATE TABLE dbo.PostalCodes (
    CodeID     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_PostalCodes PRIMARY KEY,
    PostalCode nvarchar(10) NULL
);

The common constraint is NOT LIKE '%[^0-9]%'. The pattern [^0-9] matches one character that is not a digit. So the constraint says that the value must not contain such a character. It is a double negative, and it has the right shape. It also has two holes, shown below.

A common alternative is the positive form, LIKE '%[0-9]%'. It reads more naturally, and it is wrong. It means that the value contains at least one digit, so letters around a digit pass. The next query compares both forms with a stricter third form, on ten sample values.

SELECT v.label,
       CASE WHEN v.s NOT LIKE N'%[^0-9]%' THEN 'passes' ELSE 'rejected' END AS DoubleNegative,
       CASE WHEN v.s LIKE N'%[0-9]%' THEN 'passes' ELSE 'rejected' END AS Positive,
       CASE WHEN v.s COLLATE Latin1_General_BIN2 NOT LIKE N'%[^0-9]%' AND v.s <> N'' THEN 'passes' ELSE 'rejected' END AS Strict
FROM (VALUES (N'123', 'plain digits'), (N'0123', 'leading zero'), (N'ab3', 'letters and a digit'), (N'', 'empty string'),
             (N'12 3', 'inner space'), (N'-5', 'minus sign'), (N'1.5', 'decimal point'),
             (NCHAR(179), 'superscript three'), (NCHAR(1635), 'Arabic-Indic three'), (NCHAR(65297) + NCHAR(65298), 'full-width one and two')) AS v(s, label);

The positive form passes letters and symbols, as the second column shows. The double negative has two quiet holes. It accepts an empty string, because an empty string holds no bad character. It also accepts digits from other scripts, such as the superscript three, the Arabic-Indic three and the full-width digits. The database collation treats them as part of the range 0 to 9.

A Safer Constraint for Digits in a Column

Two changes close the holes. A binary collation compares code points, so only the characters 0 to 9 match the range. A second test rejects the empty string. The script adds the constraint and tries eight values. Together they allow only digits and nothing else. Each insert runs in its own TRY block, and the table shows the outcome.

ALTER TABLE dbo.PostalCodes ADD CONSTRAINT CK_PostalCodes_Digits
    CHECK (PostalCode COLLATE Latin1_General_BIN2 NOT LIKE N'%[^0-9]%' AND PostalCode <> N'');
GO
DECLARE @tries TABLE (label varchar(30), s nvarchar(10));
INSERT @tries VALUES ('plain digits', N'90210'), ('leading zero', N'01234'), ('letters', N'ab3'), ('empty string', N''),
                     ('inner space', N'12 3'), ('superscript three', NCHAR(179)), ('Arabic-Indic three', NCHAR(1635)), ('NULL', NULL);
DECLARE @result TABLE (label varchar(30), outcome varchar(10));
DECLARE @label varchar(30), @s nvarchar(10);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT label, s FROM @tries;
OPEN c;
FETCH NEXT FROM c INTO @label, @s;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        INSERT dbo.PostalCodes (PostalCode) VALUES (@s);
        INSERT @result VALUES (@label, 'accepted');
    END TRY
    BEGIN CATCH
        INSERT @result VALUES (@label, CASE WHEN ERROR_NUMBER() = 547 THEN 'rejected' ELSE 'error ' + CAST(ERROR_NUMBER() AS varchar(10)) END);
    END CATCH;
    FETCH NEXT FROM c INTO @label, @s;
END;
CLOSE c;
DEALLOCATE c;
SELECT label, outcome FROM @result;

The last row matters. A CHECK constraint accepts NULL, because the test returns unknown and not false. If a code is required, declare the column NOT NULL as well. The failing statement returns Msg 547. Run this single insert to see it.

INSERT INTO dbo.PostalCodes (PostalCode) VALUES (N'ab3');

Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_PostalCodes_Digits". The conflict occurred in database "DigitsOnlyDemo", table "dbo.PostalCodes", column 'PostalCode'.
The statement has been terminated.

Use varchar or nvarchar for this pattern. A char(10) column pads 123 with spaces, and the pattern rejects them. Give every constraint a name, as the script does. Without a name, SQL Server invents a long one, and the error message then points to a name nobody recognizes.

Why Not ISNUMERIC or TRY_CAST?

Both functions answer another question: can this text become a number? That is not the same as digits only. The query below runs six values through both.

SELECT v.s AS Value, ISNUMERIC(v.s) AS IsNum, CASE WHEN TRY_CAST(v.s AS bigint) IS NULL THEN 'NULL' ELSE CAST(TRY_CAST(v.s AS bigint) AS varchar(20)) END AS TryCastBigint
FROM (VALUES ('007'), ('1e5'), ('$'), ('-5'), ('1.5'), ('')) AS v(s);

ISNUMERIC says yes to an exponent, a currency sign, a minus sign and a decimal point. TRY_CAST to bigint accepts the minus sign, turns an empty string into 0, and drops the leading zeros of 007. Neither one can allow only digits in a column of codes. The pattern can.

Exactly Five Digits

A code of a fixed length needs a pattern with one bracket per digit. The next script builds a small table of five digit codes. The first insert is good. The next two fail with Msg 547, because one value is too short and one has a letter.

CREATE TABLE dbo.ZipCodes (
    ZipCode char(5) NOT NULL CONSTRAINT CK_ZipCodes_Five CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]' COLLATE Latin1_General_BIN2)
);
INSERT INTO dbo.ZipCodes (ZipCode) VALUES ('90210');
INSERT INTO dbo.ZipCodes (ZipCode) VALUES ('1234');
INSERT INTO dbo.ZipCodes (ZipCode) VALUES ('12a45');

Adding the Constraint to Existing Data

SQL Server checks the rows already in the table when you add a constraint. If one row breaks the rule, the statement fails. The script below fills a table with one good and one bad value. The first ALTER fails with Msg 547. The second one uses WITH NOCHECK and succeeds.

CREATE TABLE dbo.Legacy (Code varchar(10) NOT NULL);
INSERT INTO dbo.Legacy (Code) VALUES ('123'), ('ab3');
GO
ALTER TABLE dbo.Legacy ADD CONSTRAINT CK_Legacy_Digits CHECK (Code NOT LIKE '%[^0-9]%');
GO
ALTER TABLE dbo.Legacy WITH NOCHECK ADD CONSTRAINT CK_Legacy_Digits CHECK (Code NOT LIKE '%[^0-9]%');
SELECT name, is_not_trusted FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID(N'dbo.Legacy');

The first ALTER prints this message. Only the second one adds the constraint.

Msg 547, Level 16, State 1, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "CK_Legacy_Digits". The conflict occurred in database "DigitsOnlyDemo", table "dbo.Legacy", column 'Code'.

The value 1 means that SQL Server does not trust the constraint, because old rows were never checked. New rows are checked, but the optimizer cannot use the rule to improve plans. Clean the bad rows, then run ALTER TABLE ... WITH CHECK CHECK CONSTRAINT to make it trusted again.

Is a Pattern the Right Tool?

You could argue that digits belong in a numeric column. Many do not. A postal code with a leading zero loses the zero in an int column. A phone number is a label, not a quantity. Text with a constraint keeps the value as typed. I use a CHECK constraint for codes and a numeric type for amounts.

What to Remember

For digits in a column, use NOT LIKE '%[^0-9]%' with a binary collation. Add a test for the empty string. Declare the column NOT NULL when a value is required. Add a length test for fixed codes. Test odd values before you trust a pattern.

When you finish, run the cleanup script. It removes the demo database.

USE master;
GO
ALTER DATABASE DigitsOnlyDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE DigitsOnlyDemo;

A pattern is not a promise, it is a rule that holds only for the cases you tested.

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