how to Separate address1 and address2 from address

[jannat]

Hi all smart people,

TableA has a column called address.
values for example:

address
--------------------------------------
107 31 118th street apt#2
1717 east 16th st apt 3e
attn: sales dept po box 398

TableB has two fields called address1 and address2.

Values need to be in there:

address1 address2
------------------------------------
107 31 118th street apt#2
1717 east 16th st apt 3e
attn: sales dept po box 398

How can I make it happen? Please help.

Thanks,
Jannat.

[MAK]

it will be easy if it has a definite pattern. Only human can identify a pattern. if it is a variable pattern like this then we have to write a procedure to identify all known unique patterns.

You can try the following and fix the bad rows later.

create table addresstable (address varchar(100))

insert into addresstable select "107 31 118th street apt#2"
insert into addresstable select "1717 east 16th st apt 3e"
insert into addresstable select "attn: sales dept po box 398"


select left(address,6) as address1, substring(address,6,93) as address2 from addresstable

[andi_g69]

Or you make it a bit more dynamic and split after at the position of the 2nd blank:

SELECT LEFT (address, CharIndex (' ', address, CharIndex (' ', address)+1)-1)
, Right (address, Len (address) - CharIndex (' ', address, CharIndex (' ', address)+1))
However, as MAK said, you will have to check the result manually to find any record which does not follow the pattern...