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')
COALESCE(@outputstring + SUBSTRING(@inputstring, src.position, 1),
SUBSTRING(@inputstring, src.position, 1) )
(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]'
-- 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), '') + '-')), '')
---- the limerick is mine but the code belongs to Plamen Ratchev: