Phill,
Some inline views may help simplify it:
select Account, Address1 = substring(address, 1, delimpos1-1), Address2 = substring(address, delimpos1+1, delimpos2-delimpos1-1), Address3 = substring(address, delimpos2+1, delimpos3-delimpos2-1), Address4 = substring(address, delimpos3+1, delimpos4-delimpos3-1), Address5 = substring(address, delimpos4+1, address_len-delimpos4) from ( select *, delimpos4=charindex(';', address, delimpos3+1) from ( select *, delimpos3=charindex(';', address, delimpos2+1) from ( select *, delimpos2=charindex(';', address, delimpos1+1) from ( select *, address_len=len(address), delimpos1=charindex(';', address) from AddrSrc ) [Inline1] ) [Inline2] ) [Inline3] ) [Inline4]
Cheers,
- Mark