SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for June, 2011

SQL Server: Allow Only Alpha Numeric Characters to a Column

Posted by Prashant on June 16, 2011


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.

Posted in SQL Server | Tagged: , , , | Leave a Comment »

 
%d bloggers like this: