SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: