We are running MS SQL Server 2000 as the backend for our ERP system. The customer database stores the customer address (ie. 1,2&3) as a single record with carriage returns and linefeeds, rather than breaking it into separate records. I am trying to write a query that will break the address field into a field for each line (at the carriage return or linefeed) so that we can link the UPS shipping computer directly to a OBDC query view of our sales orders.
The record currently looks something like:
Address:
Attn. Purchasing
Biggy Corporation
123 Some Street
I want it to look like:
Address1:
Attn. Purchasing
Address2:
Biggy Corporation
Address3:
123 Some Street
If someone could give me a few pointers or examples to work with on this, It would really save my neck.
Thanks A lot
I don't know if you people have any knowledge of Perl, but here is a solution:
$ perl test.pl
address1 > Attn. Purchasing
address2 > Biggy Corporation
address3 > 123 Some Street
You would tackle the broader problem with a SQL SELECT to get all the address, load the address ( with the key ) into an array, then loop through the array to insert the split parts in the new fields.
You can modify this to fit just about any programming language.
The code that Claire gave me (Thanks Claire!) didn't like it when there wasn't a char(13) (some addresses only had one address line)
I was able to do it with three select statements and unions.
I had to fool around with where the charindex began looking for the next line to get it so that address 2 and 3 would not already have a carriage return at the beginning.