x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

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

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

Feedback

Gravatar

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

# re: Extract Just Numbers from a String

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

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

Powered by:
Powered By Subtext Powered By ASP.NET