Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

Extract Just Numbers from a String

I guess you could modify this to Extract whatever you want.  Here's the thread with the original question. Now I'm not gonna say this is real effecient, but I thought it was a little clever.  Can anyone come up with a full blow set based solution?  I'm sure it'd be ok for an overnight batch process, or used in conjunction with bcp and QUERYOUT, then just bcp.  Now I didn't ask why anyone would need to do this.  I wonder if it was a homework question.  I didn't think to ask, I just got caught up with the challenge.  And I can use all the number table exercises I can get.

 

EDIT: Well, all I can say is that it helps to read these thing.  TG, already posted the correct answer in that thread.  Mea Culpa.

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE numbers(n int ,[CHAR] char(1))
GO

DECLARE @n int
SET NOCOUNT ON
SELECT @n = 48
WHILE @n < 58
  BEGIN
 INSERT INTO numbers(n, [CHAR]) SELECT @n, CHAR(@n)
 SELECT @n = @n + 1
  END
GO

CREATE FUNCTION udf_x(@x varchar(8000))
RETURNS varchar(8000)
AS
  BEGIN
 DECLARE @n int, @MAX_n int, @z varchar(8000)
 DECLARE @y  table(pos int, value char(1))
 SELECT @MAX_n = LEN(@x), @n = 1, @z = ''
 WHILE @N<
=@MAX_n
   BEGIN
  INSERT INTO @y(pos,value) SELECT @n, SUBSTRING(@x,@n,1)
  SELECT @n = @n + 1
   END
 SELECT @z = @z + [CHAR] FROM @y JOIN numbers ON [CHAR] = value ORDER BY pos
  RETURN @z
  END
GO

SELECT dbo.udf_x('123 Main Apt 456')
GO

SELECT dbo.udf_x(ShipAddress) FROM Orders
GO

SET NOCOUNT OFF
DROP TABLE numbers
DROP FUNCTION udf_x
GO

Legacy Comments


Adam Machanic
2005-06-23
re: Extract Just Numbers from a String
Try this one...


declare @thestring varchar(50)
set @thestring = '123 Main Apt 456'
declare @final varchar(50)
set @final = ''

select @final = @final + x.thenum
from
(
select substring(@thestring, number, 1) as thenum, number
from numbers
where substring(@thestring, number, 1) like '[0-9]'
) x
order by x.number

print @final

Lunchbox
2005-10-03
re: Extract Just Numbers from a String
Thanks alot, this works beautifully!!!

Brett
2005-12-06
re: Extract Just Numbers from a String
Adam, I trust gave it it a shot, modified it to work with my numbers table, and it returns nothing

declare @thestring varchar(50)
set @thestring = '123 Main Apt 456'
declare @final varchar(50)
set @final = ''

select @final = @final + x.thenum
from
(
select substring(@thestring, n, 1) as thenum, n
from numbers
where substring(@thestring, n, 1) like '[0-9]'
) x
order by x.n

SELECT @final AS Final