将地址字符串分解为多列

Question

我有一个街道地址字符串拉入一个表，用char(10(分隔。我正在尝试分离字符串，以便将该字段映射到另一个系统中，该系统具有Addressline1、AddressLine2和AddressLine3。我一直在与以下公司合作：

,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

我的问题是，如果一个地址没有第三个字符(1(，那么我的列AddressLine2&AddressLine3将相同。我应该在AddressLine3上使用不同的case语句还是使用不同的子字符串语句？

街道地址示例：主街1234号123单元123号信箱

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

Answer 1

如果函数是一个选项，您可以在这里查看。下面的解决方案不使用预定义的函数。它确实包括一个递归公共表表达式。

样本数据

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');

解决方案

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;

结果和中间结果：见fiddle。

Answer 2

您是否考虑过使用PARSENAME函数？请在下面找到摘录

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