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

3 Responses

  1. […] Scott Bateman has posted his solution to extracting a distinct list of numbers from a free-form text field. […]

  2. Thanks a lot

  3. интим знакомства в Палехе

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: