Break up address string into multiple columns

Question

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

Sample data and desired results would help. – Gordon Linoff Sep 16 '20 at 14:49 — Gordon Linoff, Sep 16 '20 at 14:49

score 1 · Answer 1 · answered Sep 16 '20 at 15:27

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.

score 0 · Answer 2 · answered Sep 16 '20 at 14:53

0

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

answered Sep 16 '20 at 14:53

Ankit Das

640
4
16

I'm just getting null values – MattC Sep 16 '20 at 14:58
`char(10)` is a line feed character and not a space `' '`. This answer needs some tweaking. – Sander Sep 16 '20 at 14:59
Also, ParseName will only work well if the different parts are no longer than 128 chars. – Zohar Peled Sep 16 '20 at 15:13
I just gave the snippet , you need to modify it according to your needs. I have used the same for a similar requirement. – Ankit Das Sep 17 '20 at 06:20

Break up address string into multiple columns

2 Answers2