mysql - 如何将地址分成多个字段？

Question

我浏览了此网站，但找不到正确的答案。

我有一个名为 ADDRLINE1 的字段，用于保存联系人地址。地址像这样输入到应用程序中。

 7710 Computer Ave.
 Ste. 103
 Edina, MN 55435

每行后面有一个回车符。有时有套件 # 有时没有。

我需要将此地址分成不同的列。

   Addr1               Addr2         Addr3    City    State   ZipCode
7710 Computer Ave.    Ste. 103        NULL    Edina    MN      55435

我该怎么做？我看过一些例子，但没有一个是我想要的。

提前致谢!

Answer 1

这不是最干净、最有效的方法，我相信很可能有更好、更快的方法来做到这一点，但这应该可以帮助您，直到其他人给出更好的解决方案:

DECLARE @tbl TABLE ( ADDRLINE1 VARCHAR(100) )
INSERT  INTO @tbl
    SELECT  '7710 Computer Ave' + CHAR(13) + CHAR(10) + 'Ste. 103' + CHAR(13) + CHAR(10) + 'suite #123' + CHAR(13) + CHAR(10) + 'Edina, MN 55435'
    UNION ALL
    SELECT  '7710 Computer Ave' + CHAR(13) + CHAR(10) + 'Ste. 103' + CHAR(13) + CHAR(10) + 'Edina, MN 55435'

SELECT  SUBSTRING([ADDRLINE1], 1, CHARINDEX(CHAR(13), [ADDRLINE1]) - 1) AS Addr1 ,
    SUBSTRING([ADDRLINE1], CHARINDEX(CHAR(13), [ADDRLINE1]) + 2, CHARINDEX(CHAR(13), REPLACE([ADDRLINE1], SUBSTRING([ADDRLINE1], 1, CHARINDEX(CHAR(13), [ADDRLINE1]) + 1), '')) - 1) AS Addr2 ,
    CASE WHEN (LEN([ADDRLINE1]) - LEN(REPLACE([ADDRLINE1],CHAR(13),''))) = 3
        THEN REVERSE(SUBSTRING(REVERSE(REPLACE([ADDRLINE1],REVERSE(SUBSTRING(REVERSE([ADDRLINE1]),1,CHARINDEX(CHAR(13),REVERSE([ADDRLINE1])))),'')),
                               1,
                               CHARINDEX(CHAR(13),REVERSE(REPLACE([ADDRLINE1],REVERSE(SUBSTRING(REVERSE([ADDRLINE1]),1,CHARINDEX(CHAR(13),REVERSE([ADDRLINE1])))),'')))-2)) ELSE NULL END AS Addr3 ,
    REVERSE(SUBSTRING(REVERSE([ADDRLINE1]), CHARINDEX(',', REVERSE([ADDRLINE1])) + 1, CHARINDEX(CHAR(10), REVERSE([ADDRLINE1])) - CHARINDEX(',', REVERSE([ADDRLINE1])) - 1)) AS City ,
    SUBSTRING([ADDRLINE1], CHARINDEX(',', [ADDRLINE1]) + 2, 2) AS [State] ,
    SUBSTRING([ADDRLINE1], CHARINDEX(',', [ADDRLINE1]) + 5, 5) AS ZipCode 
FROM    @tbl

输出:

Addr1               Addr2       Addr3       City    State   ZipCode
7710 Computer Ave   Ste. 103    suite #123  Edina   MN      55435
7710 Computer Ave   Ste. 103    NULL        Edina   MN      55435

注意:“州”和“邮政编码”列基于以下假设:值中唯一的逗号位于“城市”后面。如果这不是一个有效的假设，我可以进行一些编辑。