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