# x002548's 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

GO

SET NOCOUNT OFF
DROP TABLE numbers
DROP FUNCTION udf_x
GO

Print | posted on Wednesday, June 22, 2005 5:05 PM | Filed Under [ SQL Server ]

## #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
6/23/2005 10:59 AM | Adam Machanic

## #re: Extract Just Numbers from a String

Thanks alot, this works beautifully!!!
10/3/2005 7:49 AM | Lunchbox

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

12/6/2005 3:23 PM | Brett
Comments have been closed on this topic.