Need to pull individual address lines 1, 2, 3 from single text field -- similar to VFP MLine
Assume table: customer
custnum (int), address (text)
Address contains multi-line addresses, ex:
65 Flufferwasser Lane
Mailstop 1234
Charlotte, NC 28202
I need to extract each line, in the format of addressLine1, addressLine2, addressLine3, to an output file.
Ex: select custnum, (line1 of address) as AddressLine1, select (line2 of address) as AddressLine2 from customer
In VFP it's easy -- you just use: select custnum, mline(address,1) as Addressline1, mline(address,2) as Addressline2 from customer
Any suggestions greatly appreciated. I've been struggling with nesting charindexes but feel there has to be a better way. (And yes, I'm stuck with the data in its current format!).
custnum (int), address (text)
Address contains multi-line addresses, ex:
65 Flufferwasser Lane
Mailstop 1234
Charlotte, NC 28202
I need to extract each line, in the format of addressLine1, addressLine2, addressLine3, to an output file.
Ex: select custnum, (line1 of address) as AddressLine1, select (line2 of address) as AddressLine2 from customer
In VFP it's easy -- you just use: select custnum, mline(address,1) as Addressline1, mline(address,2) as Addressline2 from customer
Any suggestions greatly appreciated. I've been struggling with nesting charindexes but feel there has to be a better way. (And yes, I'm stuck with the data in its current format!).
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
dtodd -- your way is definitely the better way, but .... due to time constraints, I went with the other solution.
mwvisa1 -- good code, but can't do len(text) so I used your nested case whens and muddled through with charindexes and substrings. Glad I only had to provide 3 levels!