Replace function and regular expressions

[Darren Jensen]

Is it possible to use the REPLACE function in SQL Server 2000 so that
it returns a string containing only alpha-numeric characters (much
like using regular expressions)?

Thank you in advance for any suggestion.

Darren.

[David Portas]

I think you'll have to do it iteratively:

CREATE FUNCTION dbo.CleanChars
(@str VARCHAR(8000), @validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @validchars + ']%',@str) > 0
SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%[^'
+ @validchars +']%',@str), 1) ,'')
RETURN @str
END

GO

SELECT dbo.CleanChars('TESTING1234','0-9')

--
David Portas
SQL Server MVP
--