Friday, November 14, 2008

SQL String Parsing Function

The following is a SQL function for parsing a string into its alphanumeric characters. It can be called as dbo.funcParseAlphaChars(@string)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER
FUNCTION [dbo].[funcParseAlphaChars]
(
@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: