Monday, December 14, 2009

How to remove special characters in a string using SQL Server?

CREATE FUNCTION dbo.Fun_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
GO


Test
-----
SELECT dbo.Fun_ParseAlphaChars('123k@nn@/;')

Output:
-------
123knn

No comments:

 
Feedback Form