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.

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.

• Zoom in SSMS – SQL in Sixty Seconds #153
• Transfer Schema of Table – SQL in Sixty Seconds #152
• Find a Table in Execution Plan – SQL in Sixty Seconds #151
• Transactions and Variables – SQL in Sixty Seconds #150
• Count Table in Cache – SQL in Sixty Seconds #149
• List All Sessions – SQL in Sixty Seconds #148
• Line Numbers for SSMS Efficiency – SQL in Sixty Seconds #147
• Slow Running Query – SQL in Sixty Seconds #146
• Change Database and Table Collation – SQL in Sixty Seconds #145
• Infinite Loop – SQL in Sixty Seconds #144
• Efficiency Trick – Query Shortcut – SQL in Sixty Seconds #143
• SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison – SQL in Sixty Seconds #142
• SQL SERVER – TOP and DISTINCT – Epic Confusion – SQL in Sixty Seconds #141

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

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