SQL SERVER – CHECK CONSTRAINT to Allow Only Digits in Column

Today we will see a very simple script that I recently implemented for a column for my client during Comprehensive Database Performance Health Check. Today we will learn about CHECK CONSTRAINT to Allow Only Digits in Column.

SQL SERVER - CHECK CONSTRAINT to Allow Only Digits in Column checkconstraint-800x336

For example, if you have a TestTable with a column named DigiColumn. If you want to allow only numerical values in it, you will have to apply the CHECK CONSTRAINT on it, and here is how you can do it.

CREATE TABLE [dbo].[TestTable](
[DigiColumn] [nvarchar](10) NULL
) ON [PRIMARY]
GO

Now here is the constraint you can apply on this TestTable so it will only allow digits.

ALTER TABLE [dbo].[TestTable]
ADD CONSTRAINT DigiConstraint
CHECK ([DigiColumn] NOT LIKE '%[^0-9]%')

This means whenever you try to insert any other value than digits, it will give you an error.

Try to execute the following script and it will work successfully as it only contains numbers.

INSERT INTO TestTable (DigiColumn)
VALUES ('123')
GO

Try to execute the following script and it will give you an error as it contains alphabets.

INSERT INTO TestTable (DigiColumn)
VALUES ('ab3')
GO

Msg 547, Level 16, State 0, Line 23
The INSERT statement conflicted with the CHECK constraint “DigiConstraint”. The conflict occurred in database “CollationTest”, table “dbo.TestTable”, column ‘DigiColumn’.
The statement has been terminated.

I hope you enjoyed today’s quick blog post about CHECK CONSTRAINT to Allow Only Digits in Column.

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

I hope you enjoyed this video about the Zoom in SSMS.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Constraint and Keys, SQL Function, SQL Scripts, SQL Server, SQL String
Previous Post
SQL SERVER – Find a Table in Execution Plan
Next Post
SQL SERVER – Hide Code in SSMS

Related Posts

3 Comments. Leave new

  • The constraint in the example uses a double negative.
    The “positive” version of the constraint also works and may be more intuitive.

    ALTER TABLE [dbo].[TestTable]
    ADD CONSTRAINT DigiConstraint
    CHECK ([DigiColumn] LIKE ‘%[0-9]%’)

    Reply
    • Try out the suggestion, you will find that when you try to insert
      INSERT INTO TestTable (DigiColumn)
      VALUES (‘ab3’)
      GO

      It will work fine.

      Reply
  • Nice trick! Thanks. It’s good to think within a “set theory” mindset to understand what it does.

    Reply

Leave a Reply