Splitting Numbers and Characters

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 2005 Forums

Transact-SQL (2005)

Splitting Numbers and Characters

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2007-06-06 : 08:40:11

Hi Friends

I am trying to split then numbers and characters from an address1 field.

10,Downing Street
11 Richard Stree
123 Thames house

I am trying to figure out how to split the numbers to a seperate Column.

Regards
Vic

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 08:49:33

[code]select
a, substring(a,start,length) as [num only]
from
(select a, patindex('%[0-9]%', a) as start, patindex('%[^0-9]%', a)-1 as length from
(
select '10,Downing Street' as a union all
select '11 Richard Stree' union all
select '123 Thames house') t
) t
where start>0 and length>start[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

bronwyn
Starting Member

3 Posts

Posted - 2007-06-06 : 08:52:36

You could try this:

declare @Number as int,
@value as varchar(50)

set @value = '10,Downing Street'

--Replace any commas with spaces
set @value = replace(@value, ',', ' ')

--Search for spaces
set @number = substring(@value, 1, charindex(' ', @value, 1))
select @number

svicky9
Posting Yak Master

232 Posts

Posted - 2007-06-06 : 08:56:32

Thanks for the Answers but i have a table of 10000 Records which have values like that

say the table name is xyz and colname is address1....How do i write a generic query which will pick up the numeric characters from the address.

Vic

http://vicdba.blogspot.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 09:07:42

[code]select
address1, substring(address1,start,length) as [num only]
from
(select address1, patindex('%[0-9]%', address1) as start, patindex('%[^0-9]%', address1)-1 as length from
xyz) t
where start>0 and length>start[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

bronwyn
Starting Member

3 Posts

Posted - 2007-06-06 : 09:09:02

--You could try this:
-- provided you have a unique index from 1 to 1000

declare @Number as int,
@value as varchar(50),
@recCount as int,
@counter as int

set @counter = 0
select @recCount = max(UniqueID) from xyz

while @counter < @recCount
begin

select @value = address1 from xyz where UniqueID = @counter
--Replace any commas with spaces
set @value = replace(@value, ',', ' ')

--Search for spaces
set @number = substring(@value, 1, charindex(' ', @value, 1))
--insert into new table

set @counter = @counter + 1
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-06 : 10:04:21

Also, if you want to show the data in front end you can easily make use of split function there (ex Vb6)

Madhivanan

Failing to plan is Planning to fail

CraigGadke
Starting Member

1 Post

Posted - 2007-06-07 : 11:38:37

svicky9,

I am not sure if this will be a problem, but what happens if you have an address that contains alphanumeric data such as N320 Downing Street?

Like I said, it might not be a problem but you may want to verify.

Just a thought.

Craig

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources