拆分地址字段

Question

我有一个地址列，其中包含街道地址以及邮政信箱号码。我想将街道地址和邮政编码提取到单独的列中，我该怎么做？

示例数据

1 ABC CDE PO BOX 650
15 N. MAIN STREET P.O. BOX 009
(ABC)PO BOX 5909 
1 TAKEWAY PLAZA          
PO BOX 146012  Parkway STREET

期望输出：-

STREET ADDRESS               ADDRESS2

1 ABC CDE                    P.O. BOX 650
15 N. MAIN STREET            P.O. BOX 009
ABC                          P.O. BOX 5909
1 TAKEWAY PLAZA
Parkway STREET               P.O. BOX 146012

Answer 1

解析地址可能是一个滑坡。 也就是说，您似乎正在关闭PO Box，因此这使它更具可管理性，

也就是说，您可能需要考虑使用 GOOGLE API 来规范数据库中的地址地址标准化

这将通过规范化和清理字符串来产生所需的结果。 然后是解析字符串，然后通过XML和Stuff()重建它的简单问题。

有很多可用的解析/拆分函数，我在下面提供了我的

例

Declare @YourTable table (address varchar(250))
Insert Into @YourTable values
('1 ABC CDE PO BOX 650'),
('15 N. MAIN STREET P.O. BOX 009'),
('(ABC)PO BOX 5909'),
('1 TAKEWAY PLAZA'),          
('PO BOX 146012  Parkway STREET')
Select A.*
,C.*
From  @YourTable A
Cross Apply (
Select CleanString = replace(replace(replace(replace(replace(replace(replace(A.Address,'  ',' '),'P. O','P.O'),'P.','P'),'O. ','O'),'O BOX','OBOX'),'BOX ','BOX'),'POBOX',' POBOX')+' '
) B
Outer Apply (
Select Address1 = Stuff((Select ' ' +RetVal 
From  [dbo].[udf-Str-Parse](B.CleanString,' ')
Where RetVal Not Like 'POBOX%'
For XML Path ('')),1,1,'')
,Address2 = (Select replace(RetVal,'POBOX','P.O. Box ') From  [dbo].[udf-Str-Parse](B.CleanString,' ') Where RetVal Like 'POBOX%')
) C

返回

address                         Address1            Address2
1 ABC CDE PO BOX 650            1 ABC CDE           P.O. Box 650
15 N. MAIN STREET P.O. BOX 009  15 N. MAIN STREET   P.O. Box 009
(ABC)PO BOX 5909                (ABC)               P.O. Box 5909
1 TAKEWAY PLAZA                 1 TAKEWAY PLAZA     NULL
PO BOX 146012  Parkway STREET   Parkway STREET      P.O. Box 146012

UDF 如果有兴趣

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')