在SQL视图中将地址列拆分为单独的列

Question

在SQL视图中将地址列拆分为单独的列

sqlsql-serverdatabasesql-server-2005

5

我在一个表中有一个地址列，需要将其在SQL Server 2005视图中拆分成多个列。我需要使用换行符chr(10)拆分该列。该列中可能有1到4行（0到3个换行符）。以下是我需要做的一些示例。最简单的方法是什么？

Examples:

Address                 Address1      Address2       Address3            Address4
------------        =   -----------   -----------    -----------------   ---------
My Company              My Company     123 Main St.  Somewhere,NY 12345  
123 Main St.         
Somewhere,NY 12345

Address                 Address1       Address2      Address3      Address4
------------        =   ------------   ----------    -----------   ---------
123 Main St.            123 Main St.

- Jamie

数据有没有可能不是存储在单个列中？ - OMG Ponies

所有地址行都存储在单个列中。 - Jamie

3个回答

1

这真是太糟糕了...我强烈建议，如果您想单独处理每个地址行，请在第一时间正确存储它。而不是继续做你正在做的事情，添加额外的列，修复现有数据一次（而不是每次运行查询时“修复”），然后调整执行插入/更新的存储过程，使其知道使用其他列。

DECLARE @Address TABLE(id INT IDENTITY(1,1), ad VARCHAR(MAX));

INSERT @Address(ad) SELECT 'line 1
line 2
line 3
line 4'
UNION ALL SELECT 'row 1
row 2
row 3'
UNION ALL SELECT 'address 1
address 2'
UNION ALL SELECT 'only 1 entry here'
UNION ALL SELECT 'let us try 5 lines
line 2
line 3
line 4 
line 5';

SELECT
    id,
    Line1 = REPLACE(REPLACE(COALESCE(Line1, ''), CHAR(10), ''), CHAR(13), ''),
    Line2 = REPLACE(REPLACE(COALESCE(Line2, ''), CHAR(10), ''), CHAR(13), ''),
    Line3 = REPLACE(REPLACE(COALESCE(SUBSTRING(Rest, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), Rest), 0), LEN(Rest))), ''), CHAR(10), ''), CHAR(13), ''),
    Line4 = REPLACE(REPLACE(COALESCE(SUBSTRING(Rest, NULLIF(CHARINDEX(CHAR(10), Rest) + 1, 1), LEN(Rest)), ''), CHAR(10), ''), CHAR(13), '')
FROM

(
    SELECT 
        id,
        ad,
        Line1,
        Line2 = SUBSTRING(Rest, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), Rest), 0), LEN(Rest))),
        Rest = SUBSTRING(Rest, NULLIF(CHARINDEX(CHAR(10), Rest) + 1, 1), LEN(Rest))
    FROM
    (
        SELECT
            id,
            ad,
            Line1 = SUBSTRING(ad, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), ad), 0), LEN(ad))),
            Rest = SUBSTRING(ad, NULLIF(CHARINDEX(CHAR(10), ad) + 1, 1), LEN(ad))
        FROM
            @address
    ) AS x
) AS y
ORDER BY id;

Denis的PARSENAME()技巧当然更整洁，但是你必须非常小心地使用一个在数据中自然出现绝对不可能的替换字符。插入符号(^)可能是一个不错的选择，但就像我说的，你需要小心谨慎。

还有一些软件包可以清理地址和其他人口统计数据，但是清理数据输入是我要继续强调的最重要的事情……如果每个地址行需要分别处理，请将它们存储在这样的方式。

- Aaron Bertrand

我完全百分之百地同意，但在这种特定情况下，我无法控制数据的结构。这很令人沮丧，但事实就是如此。 - Jamie

1

只要用户愿意每次运行查询时等待视图执行此拆分操作...那么我想你是对的，这就是它的本质（糟糕的设计）。 - Aaron Bertrand

0

在SQL中解析文本并不好玩。如果我必须做这样的事情，我会将该列导出为CSV文本文件，并在像Perl/PHP/Python这样的脚本语言中解析它。这样我就可以利用脚本语言的内置字符串函数和正则表达式。

- Yada

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- SQLMenace · Accepted Answer

使用parsename函数拆分地址，并结合COALESCE获取正确列中的信息

如果您有超过4行，此方法将无法工作

编辑：添加了反转顺序的代码

    create table #test (address varchar(1000))

    --test data
    insert #test values('My Company
    123 Main St.         
    Somewhere,NY 12345')

    insert #test values('My Company2
    666 Main St.  
    Bla Bla       
    Somewhere,NY 12345')

    insert #test values('My Company2')

    --split happens here
                            select
replace(parsename(address,ParseLen +1),'^','') as Address1,
replace(parsename(address,ParseLen ),'^','') as Address2,
replace(parsename(address,ParseLen -1),'^','') as Address3,
replace(parsename(address,ParseLen -2),'^','') as Address4
from(
select case  ascii(right(address,1)) when 10 then
replace(replace(left(address,(len(address)-1)),'.','^'),char(10),'.')  
else 
replace(replace(address,'.','^'),char(10),'.') end as address,
case  ascii(right(address,1)) when 10 then
len(replace(replace(address,'.','^'),char(10),'.')) -
len(replace(replace(address,'.','^'),char(10),'')) -1
else
len(replace(replace(address,'.','^'),char(10),'.')) -
len(replace(replace(address,'.','^'),char(10),'')) end as ParseLen
 from #test) x