Monday, July 9, 2007

UDF to parse the numeric values from a String

This UDF will parse a string and will return the numeric values that are present in the string in the same sequence that they have in string.

CREATE FUNCTION dbo.UDF_ParseNumeric
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET @string = @string
RETURN @string
END

TEST:
SELECT dbo.UDF_ParseNumeric('san1as2,.?"< \{4}[]6>:')
Result:
1246