Create rule to restrict special characters in table in sql server

Question

I want to create a rule to restrict special characters to be entered into a column. I have tried the following. But it didnt work.

CREATE RULE rule_spchar
AS
@make LIKE '%[^[^*|\":<>[]{}`\( );@&$]+$]%'

I dont know what I am doing wrong here. Any help would be appreciated.

Solution 1

Your can create a Check Constraint on this column and only allow Numbersand Alphabets to be inserted in this column, see below:

Check Constraint to only Allow Numbers & Alphabets

ALTER TABLE Table_Name 
ADD CONSTRAINT ck_No_Special_Characters 
       CHECK (Column_Name NOT LIKE '%[^A-Z0-9]%') 

Check Constraint to only Allow Numbers

ALTER TABLE Table_Name 
ADD CONSTRAINT ck_Only_Numbers 
       CHECK (Column_Name NOT LIKE '%[^0-9]%') 

Check Constraint to only Allow Alphabets

ALTER TABLE Table_Name 
ADD CONSTRAINT ck_Only_Alphabets 
       CHECK (Column_Name NOT LIKE '%[^A-Z]%') 

Solution 2

It's important to remember Microsoft's plans for the features you're using or intending to use. CREATE RULE is a deprecated feature that won't be around for long. Consider using CHECK CONSTRAINT instead.

Also, since the character exclusion class doesn't actually operate like a RegEx, trying to exclude brackets [] is impossible this way without multiple calls to LIKE. So collating to an accent-insensitive collation and using an alphanumeric inclusive filter will be more successful. More work required for non-latin alphabets.

M.Ali's NOT LIKE '%[^A-Z0-9 ]%' Should serve well.