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.
Your can create a Check Constraint on this column and only allow Numbersand Alphabets to be inserted in this column, see below:
ALTER TABLE Table_Name
ADD CONSTRAINT ck_No_Special_Characters
CHECK (Column_Name NOT LIKE '%[^A-Z0-9]%')
ALTER TABLE Table_Name
ADD CONSTRAINT ck_Only_Numbers
CHECK (Column_Name NOT LIKE '%[^0-9]%')
ALTER TABLE Table_Name
ADD CONSTRAINT ck_Only_Alphabets
CHECK (Column_Name NOT LIKE '%[^A-Z]%')
^*|\\":<>[]{}`\( );@&$]+$ ? –
Figurine NOT LIKE, combined with a negation in the RegExp (the ^ symbol), instead of just doing LIKE '[A-Z0-9]' or perhaps LIKE '^[A-Z0-9]$' ? –
Me x LIKE '[A-Z]' matches only a single character (implicitly len(x) = 1 must be true) and it becomes prohibitive to do x LIKE '[A-Z]' OR x LIKE '[A-Z][A-Z]' OR ... There is no way to use a regular-expression like (hah!) * modifier with LIKE. LIKE has very restricted pattern productions and there is no regular expression involved, even if ^ is also used in other (regular expression) contexts. The double-negative finds "any not-approved character" and then considers the input valid only "if there was none (ie. no not-approved characters)". –
Londalondon 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.
M.Ali's answer represents the best practice for the solution you describe. That being said, I read your question differently(i.e What is wrong with they way you're implementing the like comparison.)
'AB' LIKE '%[AB]% is true. The expression 'ZB' LIKE '%[^AB]%' is also true, since that statement is the equivalent of 'Z' LIKE '[^AB]' OR 'A' LIKE '[^AB]' Instead, you should use 'YZ' NOT LIKE '%[^AB]%' which is the equivalent of 'Y' NOT LIKE '%[^AB]%' AND 'Z' NOT LIKE '%[^AB]%'The following script demonstrates the formation of a complex wildcard statement that consists of special characters.
-- Create sample data
-- Experiment testing various characters
DECLARE @temp TABLE (id INT NOT NULL, string1 varchar(10) NOT NULL)
INSERT INTO @temp
(id,string1)
SELECT 1, '12]34'
UNION
SELECT 2, '12[34'
UNION
SELECT 3, '12_34'
UNION
SELECT 4, '12%34'
UNION
SELECT 5, '12]34'
SET NOCOUNT ON
DECLARE @SQL_Wildcard_Characters VARCHAR(512),
@Count_SQL_Wildcard_Characters INT,
@Other_Special_Characters VARCHAR(255),
@Character_Position INT,
@Escape_Character CHAR(1),
@Complete_Wildcard_Expression VARCHAR(1024)
SET @Character_Position = 1
-- Note these need to be escaped:
SET @SQL_Wildcard_Characters = '[]^%_'
-- Choose an escape character.
SET @Escape_Character = '~'
-- I added the single quote (') ASCII 39 and the space ( ) ASCII 32.
-- You could also add the actual characters, but this approach may make it easier to read.
SET @Other_Special_Characters = '*|\":<>{}`\();@&$' + CHAR(39) + CHAR(32)
-- Quick loop to escape the @SQL_Wildcard_Characters
SET @Count_SQL_Wildcard_Characters = LEN(@SQL_Wildcard_Characters)
WHILE @Character_Position < 2*@Count_SQL_Wildcard_Characters
BEGIN
SET @SQL_Wildcard_Characters = STUFF(@SQL_Wildcard_Characters,@Character_Position,0,@Escape_Character)
SET @Character_Position = @Character_Position + 2
END
-- Concatenate the respective strings
SET @Complete_Wildcard_Expression = @SQL_Wildcard_Characters+@Other_Special_Characters
-- Shows how the statment works for match
SELECT ID, string1, @Complete_Wildcard_Expression AS [expression]
FROM @temp
WHERE string1 LIKE '%['+@Complete_Wildcard_Expression+']%' ESCAPE @Escape_Character
-- Show how the statement works fo non-match
SELECT ID, string1, @Complete_Wildcard_Expression AS [expression]
FROM @temp
WHERE string1 NOT LIKE '%[^'+@Complete_Wildcard_Expression+']%' ESCAPE @Escape_Character
CREATE FUNCTION udf_checkspecial_characters(@String varchar(MAX))
RETURNS INT AS
BEGIN
DECLARE @Result INT;
SELECT @Result=(CASE WHEN @String COLLATE Latin1_General_BIN LIKE '%[(<~!@/#$%^&>)]%' THEN 1 ELSE 0 END);
RETURN @Result;
END
© 2022 - 2024 — McMap. All rights reserved.
RULE. Deprecated for years. – Bedroll