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

SQL Server 2008拆分字符串每行令牌的变量数

sql-server-2008 sql

josephj1989 · 技术社区 · 16 年前

我有一个相当简单的要求——我有一个具有以下(相关)结构的表。

with cte as(
select 1 id,'AA,AB,AC,AD' names union all
select 2,'BA,BB' union all
select 3,'CA,CB,CC,CD,CE' union all
select 4,'DA,DB,DC'
)

我想创建一个select语句,它将每个“name”列拆分为多行。

例如,第一行应该生成

1,'AA'
1,'AB'
1,'AC'
1,'AD'

我们只能用SQL来做吗?这在甲骨文中很容易做到。

2 回复 | 直到 13 年前

Aaron Silverman 16 年前

如果利用XML,则可以在一个没有自定义定义函数的查询中执行此操作:

WITH cte AS( /*your data*/
    SELECT 1 id,'AA,AB,AC,AD' names UNION ALL
    SELECT 2,'BA,BB' UNION ALL
    SELECT 3,'CA,CB,CC,CD,CE' UNION ALL
    SELECT 4,'DA,DB,DC'
)
, xmlData AS ( /*make into xml*/
    SELECT id, cast('<root><x>'+replace(names,',','</x><x>')+'</x></root>' as xml) AS theXML
    FROM cte
)
SELECT id, x.value('.','varchar(100)')  /*split up*/
FROM xmlData
CROSS APPLY  xmlData.theXML.nodes('//x') AS func(x)

dcp 16 年前

您可以创建一个返回表的拆分函数,然后从该表中进行选择。

/***************************************************************************
 **
 ** Function: split
 ** In: @ipRowData - The delimited list of items to split.
 ** In: @ipSplitOn - The delimiter which separates the items in @rowData.
 ** Returns: A table object containing the split items. The table object
 **    will have an ID and Data column, where ID is the number of the item
 **    in the original list and Data is the value of the item.
 **
 ** Description:
 **    Splits a delimited set of items and returns them
 **    as a table object.
 ***************************************************************************/
CREATE FUNCTION [dbo].[split]
(
   @ipRowData NVARCHAR(4000),
   @ipSplitOn NVARCHAR(5)
)
RETURNS @rtnValue table
(
   ID INT identity(1,1),
   Data NVARCHAR(100)
)
AS
BEGIN
   DECLARE
      @cnt INT
      Set @cnt = 1

   WHILE (Charindex(@ipSplitOn,@ipRowData)>0)
   BEGIN
      INSERT INTO @rtnValue
                ( data )
                  SELECT Data = ltrim(rtrim(Substring(@ipRowData,1,Charindex(@ipSplitOn,@ipRowData)-1)))
      SET @ipRowData = Substring(@ipRowData,Charindex(@ipSplitOn,@ipRowData)+1,len(@ipRowData))
      SET @cnt = @cnt + 1
   END

   INSERT INTO @rtnValue (data)
   SELECT DATA = ltrim(rtrim(@ipRowData))

   RETURN
END

GO

样品使用情况:

select 1,data from [dbo].split('AA,AB,AC,AD', ',');

输出:

(No column name)   data
1                  AA
1                  AB
1                  AC
1                  AD