SQL Server: Allow Only Alpha Numeric Characters to a Column

Here is how to restrict non alpha numeric characters to a column in SQL Server

--Create Demo Table
 CREATE TABLE AlphaNumDemo
 (
 DemoCode NVARCHAR(100)
 )
 GO

--Restrict Special Characters
 ALTER TABLE AlphaNumDemo
 ADD CONSTRAINT CHK_AllowAplhaNumericCharactresOnly
 CHECK (DemoCode NOT LIKE '%[^a-zA-Z0-9 ]%')
 GO

--Insert Test Data
 INSERT INTO AlphaNumDemo VALUES ('demo1')
 INSERT INTO AlphaNumDemo VALUES ('Demo1')
 INSERT INTO AlphaNumDemo VALUES ('Demo 1')
 INSERT INTO AlphaNumDemo VALUES ('Demo-1')
 INSERT INTO AlphaNumDemo VALUES ('#1Demo')
 GO

--Check Data
 SELECT DemoCode FROM AlphaNumDemo
 GO

--Cleanup
 DROP TABLE AlphaNumDemo
 GO

In the above demonstration observe the records with special characters will not be inserted to the table.

Advertisement