SQL Server String Manipulation – Removing Non-Numeric Characters

I recently had a need to get a distinct list of numbers from a freeform text field in a database.  After some analysis, my needs were met by the following approach:

CREATE FUNCTION dbo.UFNG_ONLY_DIGITS (@StrVal AS VARCHAR(max))
RETURNS VARCHAR(max)
AS
BEGIN
      WHILE PATINDEX(‘%[^0-9]%’, @StrVal) > 0
            SET @StrVal = REPLACE(@StrVal,
                SUBSTRING(@StrVal,PATINDEX(‘%[^0-9]%’, @StrVal),1),”)
      RETURN @StrVal
END

The function can then be used as follows:

SELECT dbo.UFNG_ONLY_DIGITS(‘8asdf7%87^A8876-*S’)

to return

87878876

Thanks to all who helped in my research with these posts:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/032f65303d22e343

http://bytes.com/forum/thread81464.html