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

Answer 3

If the parts are always delimited in the same way (i.e 1st char to 2nd comma, 2nd to 3rd comma, 3rd comma to end), look at this tsql code:

DECLARE @fullAddress varchar(50),
        @FirstPart varchar(50),
        @SecondPart varchar(50),
        @ThirdPart varchar(50)

SET @fullAddress = 'No170, Madapatha Road, Pillyandala, Sri Lanka' 

DECLARE @SecondCommaLocation int,
        @ThirdCommaLocation int

SELECT  @SecondCommaLocation = CharIndex(',', @FullAddress , CharIndex(',', @FullAddress , 1)+1), -- Get the index of the second comma
        @ThirdCommaLocation = CharIndex(',', @FullAddress , @SecondCommaLocation+1) -- get the index of the third comma

SELECT @FirstPart = SUBSTRING(@FullAddress,1,@SecondCommaLocation-1), -- get the part between the 1st characted and the 2nd comma
       @SecondPart = SUBSTRING(@FullAddress,@SecondCommaLocation+1,@ThirdCommaLocation-@SecondCommaLocation-1), -- get the part between the 2nd and 3rd comma
       @ThirdPart = SUBSTRING(@FullAddress, @ThirdCommaLocation+1, LEN(@FullAddress)-@ThirdCommaLocation+1) -- get tha part after the 3rd comma

SELECT @FirstPart,
       @SecondPart,
       @ThirdPart

Answer 4

SELECT DebCode = 1, DebBillAdd1 = 'No170, Madapatha Road, Pillyandala, Sri Lanka', DebBillAdd2 = null, DebBillAdd3 = null INTO test

DECLARE @c1 int, @c2 int, @c3 int, @len int

UPDATE test 
SET
@c1 = CHARINDEX(',', DebBillAdd1, 0)
,@c2 = CHARINDEX(',', DebBillAdd1, @c1 + 1)
,@c3    = (CHARINDEX(',', DebBillAdd1, @c1 + @c2 + 1) - 1)
,@len = LEN(DebBillAdd1)

,DebBillAdd1 = SUBSTRING(DebBillAdd1, 0, @c2)
,DebBillAdd2 = SUBSTRING(DebBillAdd1, @c2 + 1, @c3 - @c2)
,DebBillAdd3 = RIGHT(DebBillAdd1, @len - @c3 - 1)

FROM        test