将地址拆分为多列

I have an address field where all the address details are held in one column, I want to create some labels so need to be able to split the address into the correct format. Example :-

我有一个地址字段，其中所有地址详细信息都保存在一列中，我想创建一些标签，因此需要能够将地址拆分为正确的格式。示例： -

 ADDRESS 
PIKE ROAD, AL 36064-3401
MEMPHIS TN 38104-5802 
JAMAICA PLAIN MA 02130-2337

Need to split this column into

需要将此列拆分为

    City      State     Zip
  PIKE ROAD    AL       36064-3401
 MEMPHIS        TN       38104-5802
JAMAICA PLAIN   MA       02130-2337

I am able to extract Zip code using

我能够使用提取邮政编码

STUFF(Address, 1, Len(Address) +1- CHARINDEX(' ',Reverse(Address)), '') from abx

but I am having trouble in extracting city and state. Is it possible to split the string based on the length of words, i.e. all the Characters before the length of the word (2) goes in City and all the words with 2 characters goes in state example: - Pike Road goes into the City and AL (length is 2) in the state?

但我在提取城市和州方面遇到了麻烦。是否有可能根据单词的长度分割字符串，即单词（2）长度前的所有字符进入城市，所有带有2个字符的单词进入状态示例： - 派克路进入城市AL（长度是2）在州？

3 个解决方案

#1

As @Habo said, you only need to use LEN and SUBSTRING.

正如@Habo所说，你只需要使用LEN和SUBSTRING。

WITH Tbl AS(
    SELECT * FROM (VALUES       
        ('PIKE ROAD, AL 36064-3401'),
        ('MEMPHIS TN 38104-5802'),
        ('JAMAICA PLAIN MA 02130-2337')
    ) t(Address)
)
SELECT
    City    = SUBSTRING(Address, 0, LEN(Address) - 13),
    State   = SUBSTRING(Address, LEN(Address) - 12, 2),
    ZipCode = SUBSTRING(Address, LEN(Address) - 9, 10)
FROM Tbl

#2

This works for these three examples. As @Kevin pointed out above, this works if your data is consistent, which is, as he said, "a very big if."

这适用于这三个例子。正如@Kevin在上面指出的那样，如果你的数据是一致的，那么这是有效的，就像他说的那样，“非常大的if”。

What I did was create a subquery mimicking a table. It has one column, "x", that just has a string value. I worked backwards to get the zip code first (which you figured out), then the state, then the street address. The function(s) used to extract each piece of information build upon the previous one.

我所做的是创建一个模仿表格的子查询。它有一列“x”，只有一个字符串值。我向后工作以获取邮政编码（你想出来的），然后是州，然后是街道地址。用于提取基于前一个信息的每条信息的函数。

I haven't used SQL Server in years, so I used a web app designed to mimick SQL Server 2014.

我多年没有使用SQL Server，所以我使用了一个旨在模仿SQL Server 2014的Web应用程序。

This query should produce the table in the screenshot below:

此查询应生成以下屏幕截图中的表：

select x
, REPLACE(SUBSTRING(x, 1, LEN(x) - CHARINDEX(' ', REVERSE(x), CHARINDEX(' ', REVERSE(x)) + 1)), ',', '') as city
, SUBSTRING(x, LEN(x) - CHARINDEX(' ', REVERSE(x), CHARINDEX(' ', REVERSE(x)) + 1) + 2, 2) as state
, SUBSTRING(x, LEN(x) + 2 - CHARINDEX(' ', REVERSE(x)), CHARINDEX(' ', REVERSE(x))) as zip

FROM (
select 'PIKE ROAD, AL 36064-3401' as x 
union
select 'MEMPHIS TN 38104-5802'
union
select 'JAMAICA PLAIN MA 02130-2337'
    ) as whatever

HTH!

HTH！

Cheers,

干杯，

-Maashu

#3

You can do something like this, and it will work if your data is consistent. That is a very big IF...

你可以做这样的事情，如果你的数据是一致的，它会起作用。这是一个非常大的IF ...

DECLARE @ADDRESS NVARCHAR(255) = 'PIKE ROAD, AL 36064-3401'
DECLARE @DELIMITER CHAR(1) = ' '
DECLARE @POS INT
DECLARE @ZIP NVARCHAR(11)
DECLARE @STATE NVARCHAR(11)
DECLARE @CITY NVARCHAR(200)

-- get the occurrence of the last space
SET @POS = LEN(@ADDRESS) - CHARINDEX(@DELIMITER,REVERSE(@ADDRESS))

--set the zip code
SET @ZIP = SUBSTRING(@ADDRESS, @POS+2, 11)

--get the remaining portion of the address
SET @ADDRESS = SUBSTRING(@ADDRESS, 0, @POS+1)

--set the last space again
SET @POS =  LEN(@ADDRESS) - CHARINDEX(@DELIMITER,REVERSE(@ADDRESS))

--set the state and street
SET @STATE = SUBSTRING(@ADDRESS, @POS+2, 11)
SET @CITY = SUBSTRING(@ADDRESS, 0, @POS)

PRINT @ZIP
PRINT @STATE
PRINT @CITY

OUTPUT:

OUTPUT：

36064-3401
AL
PIKE ROAD

#1