SQL Server: Remove special characters from a string

I needed a function to remove special characters from a column. So I created below function.
CREATE FUNCTION [dbo].[fnRemoveSpecialchars]
(
    @String varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
    DECLARE @Clearstring varchar(255)
    Set @Clearstring = LTrim(RTrim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(@String,'~',''),'`',''),'?','')
    ,'>',''),'<',''),',',''),':',''),';',''),']',''),'[',''),'}',''),'{',''),'|',''),'+','')
    ,'=',''),'_',''),')',''),'(',''),'&',''),'^',''),'%',''),'$',''),'@',''),'!',''),Char(39),'')
    ,'#',''),'*',''),'"',''),'-',''),'.',''),'\',''),'/',''),' ','')))
    RETURN @Clearstring
END
Usage: SELECT 'String_with$special&chars',[dbo].[fnRemoveSpecialchars]('String_with$special&chars')
Another way to remove special characters and parse only alphanumeric characters is
CREATE FUNCTION [dbo].[UDF_ParseAlphaChars]
    (
        @string VARCHAR(8000)
    )
RETURNS VARCHAR(8000)
            AS
    BEGIN
    DECLARE    @IncorrectCharLoc SMALLINT
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
    WHILE @IncorrectCharLoc > 0
        BEGIN
        SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
        END
    SET @string = @string
    RETURN @string
    END

No comments:

Post a Comment