正在SQL Server中拆分地址

Question

我希望能够在SQL Server中拆分地址。我有一个样本地址

(10396 Whispering Pines Dr Frisco TX 75033-3807)

街道名称可能有多个名称，但邮政编码总是在最后，州在旁边，然后是城市，等等。

我想我应该先切断拉链+4，然后倒过来（取下拉链并取下。然后取下州（总是2位数）并取下它，然后取下城市。

其他的都是街道地址。我在上面包含的地址是如何存储在数据库中的。我自己也试过这样做，但我知道必须有更好的方法！

declare @streetAddress varchar(1500)
declare @zip varchar(10)
declare @state varchar(2)
declare @city varchar(250)
set @streetAddress = '10396 Whispering Pines Dr Frisco TX 75033-3807'
SET @streetAddress =  left(@streetAddress,charindex('-',@streetAddress) - 1)
SET @zip = right(@streetAddress,5)
SET @streetAddress = RTRIM(replace(@streetAddress,right(@streetAddress,5),''))
SET @state = right(@streetAddress,2)
SET @streetAddress = rtrim(replace(@streetAddress,right(@streetAddress,2),''))
SET @city = reverse(LEFT(reverse(@streetAddress),charindex(' ',@streetAddress)))
select @streetAddress,@state,@zip,@city

输出如下：

|10396 Whispering Pines Dr|TX|75033|Frisco|

Answer 1

像这样尝试

DECLARE @test VARCHAR(100)='10396 Whispering Pines Dr Frisco TX 75033-3807';
DECLARE @testRev VARCHAR(100)=REVERSE(@test);
DECLARE @pos1 INT=CHARINDEX(' ',@testRev);
DECLARE @pos2 INT=CHARINDEX(' ',@testRev,@pos1+1);
DECLARE @pos3 INT=CHARINDEX(' ',@testRev,@pos2+1); 
DECLARE @part4 VARCHAR(100)=REVERSE(LEFT(@testRev,@pos1-1));
DECLARE @part3 VARCHAR(100)=REVERSE(SUBSTRING(@testRev,@pos1+1,@pos2-@pos1));
DECLARE @part2 VARCHAR(100)=REVERSE(SUBSTRING(@testRev,@pos2+1,@pos3-@pos2));
DECLARE @part1 VARCHAR(100)= REVERSE(SUBSTRING(@testRev,@pos3,1000));
SELECT @part1, @part3,@part4,@part2

结果

10396 Whispering Pines Dr    TX     75033-3807   Frisco

编辑：另一种方法

这种方法的优点是，可以一次性获得所有零件（用空格分隔）。这将使继续使用任何逻辑来解决多字问题变得更容易。。。

DECLARE @test VARCHAR(100)='10396 Whispering Pines Dr Frisco TX 75033-3807';
DECLARE @testRev VARCHAR(100)=REVERSE(@test);
DECLARE @casted XML=(SELECT CAST('<x>' + REPLACE(@testRev,' ','</x><x>')+'</x>' AS XML));
SELECT REVERSE(@casted.value('/x[1]','varchar(max)')) AS ZIP
      ,REVERSE(@casted.value('/x[2]','varchar(max)')) AS State
      ,REVERSE(@casted.value('/x[3]','varchar(max)')) AS City
      ,ISNULL(REVERSE(@casted.value('/x[9]','varchar(max)')) + ' ','')
      +ISNULL(REVERSE(@casted.value('/x[8]','varchar(max)')) + ' ','')    
      +ISNULL(REVERSE(@casted.value('/x[7]','varchar(max)')) + ' ','')
      +ISNULL(REVERSE(@casted.value('/x[6]','varchar(max)')) + ' ','')    
      +ISNULL(REVERSE(@casted.value('/x[5]','varchar(max)')) + ' ','')    
      +ISNULL(REVERSE(@casted.value('/x[4]','varchar(max)')) + ' ','') AS TheRest  

Answer 2

由于如果城市名称中有空格，几乎不可能锻炼，因此以下是基于查找表和一些参考数据的建议http://www.geonames.org（我不能保证这个来源的完整性）

CREATE TABLE PostReference
    (
     CountryCode CHAR(2)
    ,PostalCode CHAR(5)
    ,PlaceName VARCHAR(180)
    ,AdminName1 VARCHAR(100)
    ,AdminCode1 VARCHAR(20)
    ,AdminName2 VARCHAR(100)
    ,AdminCode2 VARCHAR(20)
    ,AdminName3 VARCHAR(100)
    ,AdminCode3 VARCHAR(20)
    ,Latitude DECIMAL(7, 4)
    ,Longitude DECIMAL(7, 4)
    ,Accuracy CHAR(1)
    );
GO
BULK INSERT PostReference
FROM 'D:USUS.txt'
WITH (FIELDTERMINATOR = 't',ROWTERMINATOR = '0x0a');
GO

然后是一个（不太理想的）查询——我相信你可以改进这一点。

DECLARE @Address VARCHAR(100) = '10396 Whispering Pines Dr Frisco TX 75033-3807'
SELECT 
    LEFT(@Address, CHARINDEX(PlaceName, @Address) - 1) StreetAddress
    ,PlaceName
    ,AdminCode1 State
    ,RIGHT(@Address, LEN(@Address) - CHARINDEX(PostalCode, @Address) + 1) ZipCode
FROM PostReference
WHERE @Address LIKE '%' + PlaceName + ' ' + AdminCode1 + ' ' + PostalCode+'%'

StreetAddress                        PlaceName       State  ZipCode
------------------------------------ --------------- ------ -----------
10396 Whispering Pines Dr            Frisco          TX     75033-3807
(1 row(s) affected)

希望这个有用。