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
Another way to remove special characters and parse only alphanumeric characters is
(
@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')
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
(
@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