Extracting number from a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Extracting number from a field

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-15 : 09:26:36

I have an address field and I'm trying to extract ONLY the numbers from the field to use in another. The address field might have values like:
'123 main str'
'JLH Corporate Tower 8'

Do I have to walk through each character to LEN(field) or is there a function that would make this easier? I'm lost.

Thanks for your feedback.

Brian

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-15 : 10:09:06

You could do it character by character, or you could use the spaces as a search string with CHARINDEX and ISNUMERIC until you find ISNUMERIC = 1.

Raymond

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-15 : 11:16:24

Alternatively....remove each non-numeric character from the string....
and what's left over should be a number (if any)

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-15 : 11:35:36

Well, it might not be the prettiest solution, but here's what I came up with.

[code]
DECLARE cur_addy CURSOR
FOR
SELECT pri_loc_address_1 FROM BIZ_Hold
OPEN cur_addy

DECLARE @numbers varchar(128)
DECLARE @address varchar(128)
DECLARE @pos int

FETCH NEXT FROM cur_addy INTO @address
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @pos = 1
SET @numbers = ''
WHILE @pos < DATALENGTH(@address)
BEGIN
IF PATINDEX('[0-9]',SUBSTRING(@address, @pos, 1)) > 0
BEGIN
SET @numbers = @numbers + (SELECT SUBSTRING(@address, @pos, 1))
END
SET @pos = @pos + 1
END
PRINT @numbers + CHAR(10) + CHAR(13) -- to show you the results
FETCH NEXT FROM cur_addy INTO @address
END
CLOSE cur_addy
DEALLOCATE cur_addy
[code/]

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-15 : 11:38:05

dang! could a moderator fix my previous post. wrong spot for the "slash"

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-15 : 11:39:17

dang! could a moderator fix my previous post. wrong spot for the "slash"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-15 : 11:41:44

[code]--create tally table for the function

set nocount on
create table dbo.numbers ( n int primary key)
declare @n int ; set @n = 1
while @n <=255
begin
insert into numbers
select @n
set @n = @n + 1
end
GO

create function dbo.fn_remove_non_numeric (@value varchar(255))
returns varchar(255)
as
begin

declare @chars table (vc char(1), list varchar(255))

insert into @chars (vc)
select substring(@value,n,1)
from dbo.numbers
where n <=len(@value)
and substring(@value,n,1) like '[0-9]'

declare @list varchar(255)
select @list = ''

update @chars
set @list = list = @list + vc

return
(
select max(list)
from @chars
)
end
GO

declare @value varchar(255)
select @value = 'JLH Corporate Tower 8'
select dbo.fn_remove_non_numeric(@value)

select @value = '123 main str'
select dbo.fn_remove_non_numeric(@value)

drop function dbo.fn_remove_non_numeric
drop table dbo.numbers
[/code]

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-15 : 11:49:52

ehorn, could I create a function with that cursor that I wrote. I mean, I know cursors should be used sparingly, but it would work, right?

Brian

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-15 : 14:03:58

I think this should do it...

Thanks for ya'lls help.

-Brian

CREATE FUNCTION OnlyNumbers (@x varchar(128))
RETURNS varchar(128)
AS
BEGIN
	DECLARE @numbers varchar(128)
	DECLARE @pos int
	SET @pos = 0
	SET @numbers = ''
	WHILE @pos < (DATALENGTH(@x) + 1)
	BEGIN
		IF PATINDEX('[0-9]',SUBSTRING(@x, @pos, 1)) > 0
		BEGIN
			SET @numbers = @numbers + (SELECT SUBSTRING(@x, @pos, 1))
		END
		SET @pos = @pos + 1
	END
	RETURN(@numbers)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources