Breaking up a string

Question

Breaking up a string

mikewillis

Old Hand

Points: 317
More actions
October 24, 2003 at 4:07 am

#81323

Hi everyone,
I have imported some data from one database to another. The original database stores the address in one column with a carriage return in between each line. For example
1 Road Street
Badgersville
UK
The new database has columns addline1, addline2, addline3 and I'd like to be able to break up the string and populate these columns. My first steps involved replacing the carriage returns with a $ to make one long string, and then using charindex to find the position of that $ and move that portion to addline2.
To do this I guess I need a cursor to loop through each record (about 4000) and am not too sure if that's the best way or how to go about it.
Any help greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mikewillis Old Hand Points: 317 More actions · Answer 1

Amazing what an hour's thought can do. Have figured it out. Probably not the most efficient way of doing it but it works!! Code below if anyone's interested.

Declare #cursor cursor fast_forward for

select app1addressline1 from campaign order by app1addressline1

open #cursor

while @@fetch_status=0

fetch next from #cursor

update campaign set app1addressline2=(substring(app1addressline1,charindex('!',app1addressline1),50))

where app1addressline1 like '%!%'

update campaign set app1addressline1=(substring(app1addressline1,1,charindex('!',app1addressline1)))

where app1addressline1 like '%!%'

update campaign set app1addressline2=substring(app1addressline2,2,50) where app1addressline2 like '!%'

update campaign set app1addressline3=(substring(app1addressline2,charindex('!',app1addressline2),50))

where app1addressline2 like '%!%'

update campaign set app1addressline2=(substring(app1addressline2,1,charindex('!',app1addressline2)))

where app1addressline2 like '%!%'

update campaign set app1addressline3=substring(app1addressline3,2,50) where app1addressline3 like '!%'

update campaign set app1city=(substring(app1addressline3,charindex('!',app1addressline3),40))

where app1addressline3 like '%!%'

update campaign set app1addressline3=(substring(app1addressline3,1,charindex('!',app1addressline3)))

where app1addressline3 like '%!%'

update campaign set app1city=substring(app1city,2,40) where app1city like '!%'

update campaign set app1county=(substring(app1city,charindex('!',app1city),30))

where app1city like '%!%'

update campaign set app1city=(substring(app1city,1,charindex('!',app1city)))

where app1addressline3 like '%!%'

update campaign set app1county=substring(app1county,2,30) where app1county like '!%'

update campaign set app1addressline1=replace(app1addressline1,'!','')

update campaign set app1addressline2=replace(app1addressline2,'!','')

update campaign set app1addressline3=replace(app1addressline3,'!','')

update campaign set app1city=replace(app1city,'!','')

update campaign set app1county=replace(app1county,'!','')

close #cursor

deallocate #cursor

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

quote:
Amazing what an hour's thought can do. Have figured it out. Probably not the most efficient way of doing it but it works!!

You don't need a cursor or REPLACE for this. Just a single INSERT...SELECT using string functions to parse the column by finding the CHAR(13) (or however it's represented in your data).

--Jonathan

johnsonj SSCrazy Points: 2159 More actions · Answer 3

I found this split function on this site a while back -- it works great.

http://www.sqlservercentral.com/scripts/contributions/157.asp

Joe Johnson

NETDIO,LLC.

Joe Johnson
NETDIO,LLC.