CODEWITHSUNDEEP
sqlsplitsql-server-2012visual-foxpro
Shafraz Khahir
Shafraz Khahir

Reputation: 145

Split Address values into separate columns

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

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?

Upvotes: 3

Views: 1527

Answers (4)

Tamar E. Granor
Tamar E. Granor

Reputation: 3937

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

Upvotes: 1

Jerry
Jerry

Reputation: 6577

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.

Upvotes: 2

Dimt
Dimt

Reputation: 2328

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

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

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

Upvotes: 0

Related Questions