SQL Function to Remove Special Characters

February 05, 2012 Blue Cloud SQL 0

In some cases, we may need to remove special characters from a input string such as title or description fields. Sql server does not have build-in function to do that. We have to create our own user-defined function. This function removes any special character from the given string.

Code Snippet

CREATE FUNCTION [dbo].[ufn_RemoveSpecialChars]
(
	@Input VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)  
BEGIN
   
   DECLARE @Output VARCHAR(MAX	)
   
   IF (ISNULL(@Input,'')='')
	  SET @Output = @Input
   ELSE
   BEGIN
   
    DECLARE @Len INT
	DECLARE @Counter INT
	DECLARE @CharCode INT
		
	SET @Output = ''
    SET @Len = LEN(@Input)
   
   SET @Counter = 1
   
	   WHILE @Counter <= @Len 
	   BEGIN
		  
		  SET @CharCode = ASCII(SUBSTRING(@Input, @Counter, 1))
		  IF @CharCode=32 OR @CharCode BETWEEN 48 and 57 OR @CharCode BETWEEN 65 AND 90 OR @CharCode BETWEEN 97 AND 122  
			 SET @Output = @Output + CHAR(@CharCode)
			 
		  SET @Counter = @Counter + 1
		  
	   END
         
   END
      
   RETURN @Output
   
END

Sample Usage

SELECT dbo.[ufn_RemoveSpecialChars]('This^ is $- sample ~text.#&*')
--Output : This is  sample text

Note

If this doesn't meet your requirement, just alter a little bit. Find the ascii code of character what you want to remove. You can check the char code with the code like follow.

SELECT ASCII('%')
-- Output : 37
SELECT ASCII('~')
-- Output : 126

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.