CODEWITHSUNDEEP
sqlsql-server-2014
MattC
MattC

Reputation: 135

Break up address string into multiple columns

I have a street address string pulling into a table, that is separated by char(10). I am trying to separate the string so that I can map that field into another system, that has Addressline1, AddressLine2, AddressLine3. I've been working with the following:

  ,case when CHARINDEX(char(10),[BillingStreet])= 0 then BillingStreet else substring(BillingStreet,1,charindex(char(10),BillingStreet)-1) end AddressLine1
  ,case when CHARINDEX(char(10),[BillingStreet])= 0 then '' else substring(BillingStreet,charindex(char(10),BillingStreet)+1,charindex(char(10),BillingStreet)-1) end AddressLine2
  ,case when CHARINDEX(char(10),[BillingStreet])= 0 then '' else reverse(substring(reverse(BillingStreet),0,charindex(char(10),reverse(BillingStreet)))) end AddressLine3

My issue is that if an address does not have a 3rd char(1), then my columns AddressLine2 & AddressLine3 will be the same. Should I use a different case statement on AddressLine3 or be using a different substring statement?

Sample street address: 1234 Main Street Unit 123 BOX 123

Desired output:
Addressline1 = 1234 Main Street
Addressline2 = Unit 123
Addressline3 = BOX 123

Upvotes: 0

Views: 208

Answers (2)

Sander
Sander

Reputation: 4042

If functions are an option, the you can have a look here. The solution below uses no predefined function. It does include a recursive common table expression.

Sample data

create table MyData
(
  Id int,
  MyAddress nvarchar(200)
);

insert into MyData (Id, MyAddress) values
(1, 'address line 1' + char(10) + 'address line 2' + char(10) + 'address line 3'),
(2, 'short address' + char(10) + 'with only 2 lines'),
(3, '1234 Main Street' + char(10) + 'Unit 123' + char(10) + 'BOX 123');

Solution

with cte as (
  select md.MyAddress, 1 as Starts, charindex(char(10), md.MyAddress) as Pos, 1 as RowNum
  from MyData md
    union all
  select cte.MyAddress, cte.Pos + 1, charindex(char(10), cte.MyAddress, cte.Pos + 1), RowNUm+1
  from cte
  where cte.Pos > 0
),
split as
(
  select cte.MyAddress,
         cte.RowNum,
         substring(cte.MyAddress, cte.Starts, case when cte.Pos > 0 then cte.Pos-cte.Starts else len(cte.MyAddress) end) as Line
  from cte
)
select p.MyAddress,
       p.[1] as Line1,
       p.[2] as Line2,
       p.[3] as Line3
from split s
pivot (max(s.Line) for s.RowNum in ([1], [2], [3])) p;

Result and intermediate result: see fiddle.

Upvotes: 1

Ankit Das
Ankit Das

Reputation: 650

Have you considered using PARSENAME function ? Please find below the snippet

    Reverse(ParseName(Replace(Reverse(BillingStreet), ' ', '.'), 1)) As Address1
  , Reverse(ParseName(Replace(Reverse(BillingStreet), ' ', '.'), 2)) As Address2
  , Reverse(ParseName(Replace(Reverse(BillingStreet), ' ', '.'), 3)) As Address3

Upvotes: 0

Related Questions