Extract numbers from a string

You see SQL string extraction techniques all over the web.  Here are a couple flavors I like.  Of course before you can use these techniques, you must first have a flawed database design.  That makes their use tantamount to an admission of failure (I know... we all inherited the poorly designed systems).

-- extract numbers from a string

-- method one returns ALL of the digits

-- method two returns the first sequence of digits

 

-- i didn't have a numbers table handy when I wrote method one, so I derived one

-- by cross joining sysobjects to itself and using row_number() over(order by so.name)

-- of course this means it won't work on sql server 2000 or earlier

 

DECLARE @inputstring VARCHAR(200), @outputstring VARCHAR(200)

SET @inputstring = 'seven million 312 thousand 987'

 

 

--- get all digits from the input string ('555-1212' returns '5551212')

SELECT

   @outputstring = 

      COALESCE(@outputstring + SUBSTRING(@inputstring, src.position, 1),

               SUBSTRING(@inputstring, src.position, 1) )

FROM

   (SELECT ROW_NUMBER() OVER(ORDER BY so.NAME) AS position

    FROM sysobjects AS so CROSS JOIN sysobjects AS so2) AS src

WHERE SUBSTRING(@inputstring, src.position, 1) LIKE '[0-9]'

 

SELECT @outputstring

 

 

-- get the first group of digits from the input string ('555-1212' returns '555')

SELECT

-- let's get our string's first number group

-- and ignore all the alphabet soup

-- then if we find some more digits

-- we'll drop them like widgets

-- cause we STUFF-ed them all out sans a loop

   NULLIF(

   SUBSTRING(@inputstring,                            

   PATINDEX('%[0-9]%', @inputstring),                  

   PATINDEX('%[^0-9]%',                               

   STUFF(@inputstring, 1,                             

   PATINDEX('%[0-9]%', @inputstring), '') + '-')), '')

---- the limerick is mine but the code belongs to Plamen Ratchev:

---- http://www.eggheadcafe.com/conversation.aspx?messageid=33699815&threadid=33699786