declare @address table (address varchar(100))
insert into @address
	select '1746 Wellesley' union
	select 'P.O. Box 3175' union
	select '4141 65th Street' union
	select '773 Florecita Terrace'

select 	address,
	-- patindex('%[0-9]%', address + space(1)) as 'start',
	-- charindex(' ', address + space(1), patindex('%[0-9]%', address)) as 'length',
	case 
		when patindex('%[^0-9]%' , address) > 0
                then substring(address, patindex('%[0-9]%', address), charindex(' ', address + space(1), patindex('%[0-9]%', address)))
		else address
	end as 'numeric'
from @address

------------------------------------------------------------------------
if object_id('dbo.fnNumbersFromStr') > 0
	drop function dbo.fnNumbersFromStr
go

create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
as
begin
	declare @n varchar(8000)
	declare @Numbers table (rowid int identity(1,1), c varchar(1))

	insert @Numbers (c)
	select	substring(string,n,1)
	from	(select @str string) a
	cross join 
		(--any number table, better if it exists
		--this one is derived with just 32 numbers
		select n1+n2+n3+n4+n5 n
		from
			(select 0 n1 union select 1) n1
			,(select 0 n2 union select 2) n2
			,(select 0 n3 union select 4) n3
			,(select 0 n4 union select 8) n4
			,(select 0 n5 union select 16) n5
		) b
	where	n <= len(string)
	and	substring(string,n,1) like '[0-9]'
	order by n

	select @n = coalesce(@n + c, c)
	from	@Numbers
	order by rowid

	return @n
end
------------------------------------------------------------------------
--DDL/DML
set nocount on
declare @address table (Address varchar(200))

insert @address
select '1746 Wellesley' union
select 'P.O. Box 3175' union
select '4141 65th Street' union
select '773 Florecita Terrace' union
select 'ROUTE 4 BOX 454' union
select '709 HIGH TOP RD PO BOX 92' union
select '4970 SABAL PALM BLVD. #208'

-----------------------------------------------------------------------

--Statement to get just the numbers from @address
select address, dbo.fnNumbersFromStr(address) JustTheNumbers
from	@Address

declare @address table (address varchar(100))
insert into @address
	select '1746 Wellesley' union
	select 'P.O. Box 3175' union
	select '4141 65th Street' union
	select '773 Florecita Terrace' union
	select 'ROUTE 4 BOX 454' union
	select '709 HIGH TOP RD PO BOX 92' union
	select '4970 SABAL PALM BLVD. #208'

select 	address,
	-- patindex('%[0-9]%', address) as 'start',
	-- charindex(space(1), address + space(1), patindex('%[0-9]%', address)) - (patindex('%[0-9]%', address)) as 'length',
	case 
		when patindex('%[^0-9]%' , address) > 0
                then substring(address, patindex('%[0-9]%', address), charindex(space(1), address + space(1), patindex('%[0-9]%', address)) - (patindex('%[0-9]%', address)))
		else address
	end as 'numeric'
from @address

quote:

---

Originally posted by lane0618

I want to be able to return only the numbers from an address field. What is the best way to do this?

Address Field:
1746 Wellesley
P.O. Box 3175
4141 65th Street
773 Florecita Terrace

Should return:
1736
3177
415165
770

---

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

quote:

---

Originally posted by nathans

both udfs look right on.

---

quote:

---

You see 2?

---

quote:

---

You see 2?

Damn is it 5:00 ALREADY?

Did you start without me?

---

create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
as
begin
	while patindex('%[^0-9]%',@str)>0
		Set @str = rtrim(ltrim(replace(@str,substring(@str,patindex('%[^0-9]%',@str),1),'')))
	return @str
end
go

create function dbo.fnNumbersFromStr(@str varchar(8000))
returns varchar(8000)
as
begin
	while patindex('%[^ 0-9]%',@str)>0
		Set @str = replace(replace(replace(rtrim(ltrim(replace(@str,substring(@str,patindex('%[^ 0-9]%',@str),1),''))),' ',' þ'),'þ ',''),'þ','')
	return @str
end
go