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.
CREATE FUNCTION [dbo].[ufn_RemoveSpecialChars]
DECLARE @Output VARCHAR(MAX )
SET @Output = @Input
DECLARE @Len INT
DECLARE @Counter INT
DECLARE @CharCode INT
SET @Output = ''
SET @Len = LEN(@Input)
SET @Counter = 1
WHILE @Counter <= @Len
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
SELECT dbo.[ufn_RemoveSpecialChars]('This^ is $- sample ~text.#&*')
--Output : This is sample text
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.
-- Output : 37
-- Output : 126
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.