代码之家 › 专栏 › 技术社区 › gyurisc

如何拆分单元格内容并将信息提取到SQL Select语句的新列中?

sql-server-2005 sql

gyurisc · 技术社区 · 14 年前

我有一个表,其中一列存储用分号分隔的键/值对,如下所示:

KEY1:VALUE1;KEY2:VALUE2;KEY3:VALUE3

我想构建一个视图,在其中我有额外的列,值将从上面的字段中提取。我的问题是如何在select查询中提取value1、value2和value3。

此字段中的键值对不超过三个。

2 回复 | 直到 13 年前

Community c0D3l0g1c 7 年前

我今天已经回答了一个非常相似的问题,所以看看答案:

SQL comma delimted column => to rows then sum totals?

但是试试这个:

I prefer the number table approach to split a string in TSQL

要使此方法工作,需要执行此一次性时间表设置:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置好数字表后,创建此拆分函数:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

现在,您可以轻松地将csv字符串拆分为一个表并加入到其中:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

输出:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

现在可以使用交叉应用来拆分表中的每一行,如下所示:

DECLARE @YourTable table (RowID int, RowValue varchar(200))
INSERT INTO @YourTable VALUES (1,'KEY11:VALUE11;KEY12:VALUE12;KEY13:VALUE13')
INSERT INTO @YourTable VALUES (2,'KEY21:VALUE21;KEY22:VALUE22;KEY23:VALUE23')
INSERT INTO @YourTable VALUES (3,'KEY31:VALUE31;KEY32:VALUE32;KEY33:VALUE33')


SELECT
    o.RowID,RIGHT(st.ListValue,LEN(st.ListValue)-CHARINDEX(':',st.ListValue)) AS RowValue
    FROM @YourTable  o
        CROSS APPLY  dbo.FN_ListToTable(';',o.RowValue) AS st

输出:

RowID       
----------- -------
1           VALUE11
1           VALUE12
1           VALUE13
2           VALUE21
2           VALUE22
2           VALUE23
3           VALUE31
3           VALUE32
3           VALUE33

(9 row(s) affected)

treaschf 14 年前

编写一个标量函数,它接收包含键/值对的字符串,以及要获取的值的索引(或要获取的值的键)作为其参数,并返回适当的值,这将使问题容易解决。

您还可以在.NET中编写这个标量函数,它的性能要比在TSQL中编写好得多,因为您不必从该函数中访问任何表或数据库对象。