Replace function and regular expressions

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.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
--