Split Address values into separate columns

Question

I am using a SQL Server 2012 and i have a table with following structures

DebCode,DebName,DebBillAdd1,DebBillAdd2,DebBillAdd3

But in my DebBillAdd1 column contains complete address of debtors like below.(DebBillAdd2 and DebBillAdd3 are empty for all records)

No170, Madapatha Road, Pillyandala, Sri Lanka
91/1, Canal Road, Hethdala, Wattala.

Now i need to split the address into 3 parts and update them in remaining DebBillAdd2 and DebBillAdd3 columns.

This is how i need the addresses to be split ted.

DebBillAdd1 - No170, Madapatha Road
DebBillAdd2 - Piliyandala
DebBillAdd3 - Sri Lanka

DebBillAdd1 - 91/1, Canel Road
DebBillAdd2 - Hethdala
DebBillAdd3 - Wattala

• DebBillAdd1 - The SUBSTRING() has to retrieve the string between the 1st character and first two commas.
• DebBillAdd2 - The SUBSTRING() has to retrieve the string between the next two commas
• DebBillAdd3 - The SUBSTRING() has to retrieve the remaining string.

i am using Visual Foxpro for for development.

As i am not aware of splitting i could not make it out. Can you guys explain me on how to do this?

Answer 1

You can use the Foxpro ATC() and SUBSTR() to parse out the string.

For example, to get address1:

 lcAddress1 = SUBSTR(DebBillAdd1, 1, ATC(DebBillAdd1, [,], 2)-1)

To get address2:

 lnEndPos = ATC(DebBillAdd1, [,], 3)   &&Position of third comma
 lnStartPos = ATC(DebBillAdd1, [,], 2) + 1    &&Position of second comma plus 1, could add two to remove space
 lnLength = lnEndPos - lnStartPos       &&Number of characters to get.
 SUBSTR(DebBillAdd1, lnStartPos, lnLength)    &&SUBSTR function with starting position and number of characters to get.

You can use ATC() and SUBSTR() to parse the rest of the string for address3.

Then use the VFP UPDATE or REPLACE commands to update the table's columns.

Answer 2

If you always have the commas and you never have commas anywhere else, VFP's GETWORDNUM() function makes this easy:

REPLACE ALL DebBillAdd3 WITH GetWordNum(DebBillAdd1, 4, ","), ;
        DebBillAdd2 WITH GetWordNum(DebBillAdd1, 3, ","), ;
        DebBillAdd1 WITH GetWordNum(DebBillAdd1, 1, ",") + "," + GetWordNum(DebBillAdd1, 2, ",")

Tamar