How to find Special Characters in SQL

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 

  

END