If you are developing a user interface and want to stop the user from using the special character in specific field , you can develop stored procedure to be used in the validation.
To define the special characters we can use the ASCII table to find the ranges of the special characters, from that table we can easily identify the ranges which been used to represent the special characters:
ASCII Code special characters : (33-47) , (58-74), (91-96),(123-255)
In SQL we can use ASCII function (which returns the ASCII code value of a character expression), to find the ASCII codes and check if it is in the one of the specified ranges above, if the code was in these ranges it mean the it is an special character.
You can use the following SP to validate the special characters , this SP has one input parameter represent the value which you want to validate and it has two output parameters, @STATUS to indicate if there is special characters or not (=0 no special characters, =1 special characters) ,@SpecialCharacters will return the first special character.
CREATE PROCEDURE Find_Special_Characters
-- Add the parameters for the stored procedure here
@INPUTST AS VARCHAR (250),
@STATUS AS INT OUTPUT, -- =0 no SpecialCharacters, =1 SpecialCharacters
@SpecialCharacters AS VARCHAR (50) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @STATUS=0
set @SpecialCharacters=''
declare @len integer
declare @idx integer
declare @ascValue integer
declare @stopflg integer
set @stopflg=0
set @len=len(rtrim(ltrim(@INPUTST)))
set @idx=1
while @idx<=@len and @stopflg=0
begin
set @ascValue = ascii(substring(@INPUTST, @len-(@idx-1),1) )
IF (@ascValue BETWEEN 33 AND 47)
OR (@ascValue BETWEEN 58 AND 64)
OR (@ascValue BETWEEN 91 AND 96)
OR (@ascValue BETWEEN 123 AND 255)
begin
set @stopflg=1
set @STATUS=1
set @SpecialCharacters=char(@ascValue)
end
set @idx=@idx+1
end
