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
I hope it is useful for you in somewhere else.




author's photo

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.

COMMENTS
blog comments powered by Disqus