16 March 2009

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')


   @outputstring = 

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

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



    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')


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



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


   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


No comments: