T-SQL分割字串_编程黑洞网

我有一个SQL Server 2008 R2列，其中包含一个字符串，需要用逗号分隔。我在StackOverflow上看到了很多答案，但是它们都不在R2中起作用。我确保我对任何拆分函数示例都具有选择权限。任何帮助，不胜感激。

这是我喜欢stackoverflow.com/a/1846561/227755
的百万个答案之一

您是什么意思“他们都不工作”？您能更具体一点吗？

当我执行错误的功能时，安迪确实指出了正确的方向。这就是为什么其他堆栈答案都无效的原因。我的错。

SQL中拆分字符串的可能重复项

“主数据服务”附加组件中有一个mdq.RegexSplit函数，可能会有所帮助。当然值得研究。

#1 楼

我曾经使用过此SQL，可能对您有用：-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

并使用它：-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

非常感谢Andy。我对您的脚本进行了一些小的增强，以允许函数在拆分字符串中的特定索引处返回项目。仅当您正在解析第一列的结构时，它才有用。 gist.github.com/klimaye/8147193

– CF_Maintainer
2013年12月27日13:57

我在这里的github页面上发布了一些改进（带有测试用例）。当我有足够的代表超出发布“保护”时，我将其作为此堆栈溢出线程中的答案发布

–mpag
16 Jun 16'在19:29

尽管这是一个很好的答案，但它已经过时了...程序方法（尤其是循环）是要避免的事情...值得研究新的答案...

–Shnugo
17年2月2日在10:56

这不适用于dbo.splitstring（''）中的select *

– Pasi Savolainen
17年9月18日在13:17

我完全同意@Shnugo。循环分离器可以工作，但速度非常慢。像sqlservercentral.com/articles/Tally+Table/72993这样的东西要好得多。一些其他基于集合的优秀选项可以在这里找到。 sqlperformance.com/2012/07/t-sql-queries/split-strings

– Sean Lange
18年4月26日在18:45

#2 楼

除了递归的CTE和while循环，还有谁考虑过基于集合的方法？请注意，此函数是针对该问题编写的，该问题基于SQL Server 2008和逗号作为分隔符。在SQL Server 2016及更高版本（以及兼容级别130及更高版本）中，STRING_SPLIT()是更好的选择。

CREATE FUNCTION dbo.SplitString
(
  @List     nvarchar(max),
  @Delim    nvarchar(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT [Value] FROM 
  ( 
    SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
      CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
    FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
      FROM sys.all_columns) AS x WHERE Number <= LEN(@List)
      AND SUBSTRING(@Delim + @List, [Number], DATALENGTH(@Delim)/2) = @Delim
    ) AS y
  );
GO

如果要避免长度限制如果字符串<= sys.all_columns中的行数（在SQL Server 2017中为model中的9,980；在您自己的用户数据库中更高），则可以使用其他方法派生数字，例如构建自己的数字表。在无法使用系统表或无法创建自己的系统表的情况下，也可以使用递归CTE：

）到外部查询，以免递归错误> 100个字符。

（另外，定界符必须为OPTION (MAXRECURSION 0)。仍在研究原因。）

有关拆分功能的更多信息，如果拆分来自应用程序层的字符串，为什么（并证明）while循环和递归CTE无法扩展，以及更好的替代方法：

http：/ /www.sqlperformance.com/2012/07/t-sql-queries/split-strings
http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now- with-less-t-sql
https://sqlblog.org/2010/07/07/splitting-a-list-of-integers-another-roundup

由于未解析最终值，因此在字符串末尾会有空值的情况下（例如在“ 1、2、4”中），此过程存在一个小错误。要更正此错误，应将表达式“ WHERE Number <= LEN（@List）”替换为“ WHERE Number <= LEN（@List）+ 1”。

–西尔万
2014-09-15 8:33

@SylvainL我想这取决于您想要的行为。根据我的经验，大多数人都想忽略任何结尾的逗号，因为它们并不能真正代表真实的元素（您需要多少个空白字符串的副本）？无论如何，真正做到这一点的方法-如果您将遵循第二个链接-无论如何都是在缓慢的T-SQL中逐步拆分大的丑陋字符串。

–亚伦·伯特兰（Aaron Bertrand）
2014-09-15 8:37

就像您说的那样，大多数人都想忽略任何结尾的逗号，但可惜，不是全部。我想一个更完整的解决方案是添加一个参数来指定在这种情况下的处理方式，但是我的评论只是一点点说明，以确保没有人会忘记这种可能性，因为在很多情况下这种可能性是非常真实的。

–西尔万
2014-09-15 9:02

我对该功能有一个奇怪的行为。如果我直接使用字符串作为参数，则可以使用。如果我有varchar，则没有。您可以轻松重现：将invarchar声明为varchar set invarchar ='ta; aa; qq'从[dbo]。[SplitString]（invarchar，';'）SELECT值从[dbo]。[SplitString]（'ta; aa; qq'，';'）

– Patrick Desjardins
2015年6月10日4:00在

我喜欢这种方法，但是如果sys.all_objects返回的对象数少于输入字符串中的字符数，则它将截断该字符串，并且值将丢失。由于sys.all_objects只是被用作生成行的一种手段，因此有更好的方法来执行此操作，例如这个答案。

–指关节
16-10-10在15:48

#3 楼

终于，在SQL Server 2016中的等待结束了，他们引入了分割字符串功能：该STRING_SPLIT功能已将其删除。

这里是一篇出色的性能比较文章：性能惊奇和假设：STRING_SPLIT

显然，对于那些具有更新服务器的服务器，如何回答如何分割字符串的问题很明显，但是我们中那些仍然坚持使用2008 / 2008R2的用户，将不得不在这里回答其他问题之一。

–mpag
16年6月16日在19:34

您需要查看数据库中的兼容性级别。如果小于130，则将无法使用STRING_SPLIT函数。

–路易斯·泰洪（Luis Teijon）
17年6月20日在16:06

实际上，如果兼容性不是130，并且您正在运行2016（或Azure SQL），则可以使用以下命令将兼容性设置为130：ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

– Michieal
17年12月15日在15:26

#4 楼

最简单的方法是使用XML格式。

1。将字符串转换为不带表的行

QUERY

DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','    

SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

结果

 x---------x
 | Value   |
 x---------x
 | String1 |
 | String2 |
 | String3 |
 x---------x

2。转换为具有每个CSV行ID的表中的行

SOURCE TABLE

 x-----x--------------------------x
 | Id  |           Value          |
 x-----x--------------------------x
 |  1  |  String1,String2,String3 |
 |  2  |  String4,String5,String6 |     
 x-----x--------------------------x

QUERY

-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','

SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
     FROM TABLENAME
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

结果

 x-----x----------x
 | Id  |  Value   |
 x-----x----------x
 |  1  |  String1 |
 |  1  |  String2 |  
 |  1  |  String3 |
 |  2  |  String4 |  
 |  2  |  String5 |
 |  2  |  String6 |     
 x-----x----------x

如果@String包含禁止的字符，则此方法会中断...我刚刚发布了一个解决此问题的答案。

–Shnugo
17年2月2日在10:46

#5 楼

我需要一种快速的方法来摆脱邮政编码中的+4。

UPDATE #Emails 
  SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1)) 
  WHERE ZIPCode LIKE '%-%'

没有过程...没有UDF ...只是一个严格的小内联命令，它可以执行所需的操作。不花哨，不优雅。

根据需要更改定界符，依此类推，它适用于任何情况。

这不是问题所在。 OP的值类似'234,542,23'，他们希望将其分成三行...第一行：234，第二行：542，第三行：23。在SQL中要做一件棘手的事情。

– codeulike
15年4月28日在19:32

#6 楼

如果将

WHILE CHARINDEX(',', @stringToSplit) > 0

替换为

WHILE LEN(@stringToSplit) > 0

，则可以在while循环之后删除最后一个插入！ br />

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE LEN(@stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)


if @pos = 0
        SELECT @pos = LEN(@stringToSplit)


  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 RETURN
END

这将导致最后一个元素的最后一个字符被截断。即“ AL，AL”将变为“ AL” | “ A”，即“ ABC，ABC，ABC”将变为“ ABC” | “ ABC” | “ AB”

– Microsoft开发人员
2013年4月9日在16:37

将+1附加到SELECT @pos = LEN（@stringToSplit）似乎可以解决该问题。但是，SELECT @stringToSplit = SUBSTRING（@stringToSplit，@ pos + 1，LEN（@stringToSplit）-@ pos）将返回传递给LEFT或SUBSTRING函数的无效长度参数，除非您为SUBSTRING的第三个参数加+1好。或者您可以用SET @stringToSplit = SUBSTRING（@stringToSplit，@ pos + 1，4000）替换该分配-nvarchar的MAX len为4000

–mpag
16年6月15日在23:23

我也注意到上面特里指出的问题。但是@AviG的给定逻辑是如此酷，以至于在很长的令牌列表中它不会失败。尝试进行此测试调用以验证（此调用应返回969个令牌）从dbo.splitstring（'token1，token2 , token969'）中选择*，然后尝试使用mpag给出的代码来检查结果是否相同调用上面的方法，发现它只能返回365个令牌。最后，我在上面修复了AviG的代码，并在下面以新回复的形式发布了无错误功能，因为此处的注释仅允许有限的文本。检查以我的名字答复的尝试。

– Gemunu R Wickremasinghe
18年6月23日在9:18

#7 楼

所有使用某种循环（迭代）的字符串拆分功能均具有较差的性能。应该用基于集合的解决方案替换它们。

这段代码执行得很好。

CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

如果@List包含禁止的字符，则此方法将中断。我刚刚发布了一个答案来克服此问题。

–Shnugo
17-2-2在10:44

我支持您的答复，因为您的答复使用空格作为分隔符，而投票数最高的则不

– KMC
19年7月26日在14:50

#8 楼

在禁止使用字符的情况下，使用XML元素的常用方法会中断。这是一种可以将这种方法与任何类型的字符一起使用的方法，即使以分号作为分隔符。

诀窍是，首先使用SELECT SomeString AS [*] FOR XML PATH('')正确地转义所有禁止的字符。这就是为什么我将定界符替换为魔术值以避免使用;作为定界符引起麻烦的原因。

DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX))
INSERT INTO @Dummy VALUES
 (1,N'A&B;C;D;E, F')
,(2,N'"C" & ''D'';<C>;D;E, F');

DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")!

WITH Casted AS
(
    SELECT *
          ,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' AS XML) AS SplitMe
    FROM @Dummy
)
SELECT Casted.ID
      ,x.value(N'.',N'nvarchar(max)') AS Part 
FROM Casted
CROSS APPLY SplitMe.nodes(N'/x') AS A(x)

结果

ID  Part
1   A&B
1   C
1   D
1   E, F
2   "C" & 'D'
2   <C>
2   D
2   E, F

#9 楼

我最近不得不写这样的东西。这是我想出的解决方案。它适用于任何定界符字符串，我认为它的性能会稍好一些：

CREATE FUNCTION [dbo].[SplitString] 
    ( @string nvarchar(4000)
    , @delim nvarchar(100) )
RETURNS
    @result TABLE 
        ( [Value] nvarchar(4000) NOT NULL
        , [Index] int NOT NULL )
AS
BEGIN
    DECLARE @str nvarchar(4000)
          , @pos int 
          , @prv int = 1

    SELECT @pos = CHARINDEX(@delim, @string)
    WHILE @pos > 0
    BEGIN
        SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
        INSERT INTO @result SELECT @str, @prv

        SELECT @prv = @pos + LEN(@delim)
             , @pos = CHARINDEX(@delim, @string, @pos + 1)
    END

    INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
    RETURN
END

#10 楼

使用CTE的解决方案，如果有人需要的话（除了我，显然是谁，这就是我编写它的原因）。

declare @StringToSplit varchar(100) = 'Test1,Test2,Test3';
declare @SplitChar varchar(10) = ',';

with StringToSplit as (
  select 
      ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head
    , substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail

  union all

  select
      ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head
    , substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail
  from StringToSplit
  where charindex( @SplitChar, Tail ) > 0

  union all

  select
      ltrim( rtrim( Tail ) ) Head
    , '' Tail
  from StringToSplit
  where charindex( @SplitChar, Tail ) = 0
    and len( Tail ) > 0
)
select Head from StringToSplit

#11 楼

#12 楼

这里有一个正确的版本，但我认为最好添加一点容错功能，以防它们有尾随逗号以及使它成为逗号，因此您可以将其用作功能而不是用作较大代码的一部分。以防万一您只使用一次并且不需要功能。这也适用于整数（这是我需要的），因此您可能必须更改数据类型。

DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'

--SELECT @StringToSeperate IDs INTO #Test

DROP TABLE #IDs
CREATE TABLE #IDs (ID int) 

DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)

--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
    SELECT @Position  = CHARINDEX(',', @StringToSeperate)  
    SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)

    INSERT INTO #IDs 
    SELECT @CommaSeperatedValue

    SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)

END

--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
    INSERT INTO #IDs
    SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END

SELECT * FROM #IDs

如果要在WHILE循环之前设置@StringToSeperate ='StringToSeperate +'，'，我想您也许可以消除“添加最后一个值”块。另请参阅我在github上的sol'n

–mpag
16年6月16日在19:40

这是基于哪个答案？这里有很多答案，这有点令人困惑。谢谢。

– jpaugh
16年11月1日在14:38

#13 楼

我对+ Andy Robinson的功能做了一些修改。现在，您只能从返回表中选择所需的零件：

#14 楼

如果您需要使用最少的代码来解决常见情况的快速即席解决方案，则此递归CTE两层代码可以做到：对任何查询进行CTE，您就可以将结果表b与其他表连接起来，以用于任何其他表达式。
编辑（Shnugo编写）
如果添加计数器，您将获得一个位置索引以及列表：

DECLARE @s VARCHAR(200) = ',1,2,,3,,,4,,,,5,'

;WITH
a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @s, j + 1) FROM a WHERE j > i),
b AS (SELECT SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b

结果：

DECLARE @s VARCHAR(200) = '1,2333,344,4'

;WITH
a AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @s, j+1) FROM a WHERE j > i),
b AS (SELECT n, SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b;

我喜欢这种方法。希望您不要介意，我在您的答案中直接添加了一些增强功能。随时以任何方便的方式进行编辑...

–Shnugo
18年8月14日在8:23

#15 楼

我通过将值包装到元素中来进行xml路由（M，但一切正常）：

declare @v nvarchar(max) = '100,201,abcde'

select 
    a.value('.', 'varchar(max)')
from
    (select cast('<M>' + REPLACE(@v, ',', '</M><M>') + '</M>' AS XML) as col) as A
    CROSS APPLY A.col.nodes ('/M') AS Split(a)

#16 楼

这是一个可以使用patindex在模式上进行拆分的版本，对上面的帖子进行了简单的修改。我遇到了需要拆分包含多个分隔符的字符串的情况。


alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE PATINDEX(@splitPattern, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = PATINDEX(@splitPattern, @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END
select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%');

结果看起来像这样

stringb
x
y

#17 楼

我个人使用此功能：

ALTER FUNCTION [dbo].[CUST_SplitString]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1) 
        FROM Split
        WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

#18 楼

我已经按照此处的要求开发了一个双分割器（需要两个分割字符）。可能在此线程中具有一定的价值，因为它是与字符串拆分有关的查询中引用最多的。

CREATE FUNCTION uft_DoubleSplitter 
(   
    -- Add the parameters for the function here
    @String VARCHAR(4000), 
    @Splitter1 CHAR,
    @Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
                   SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
                   MId INT,
                   SValue VARCHAR(4000))
SET @String = @String+@Splitter1

WHILE CHARINDEX(@Splitter1, @String) > 0
    BEGIN
       DECLARE @WorkingString VARCHAR(4000) = NULL

       SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
       --Print @workingString

       INSERT INTO @FResult
       SELECT CASE
            WHEN @WorkingString = '' THEN NULL
            ELSE @WorkingString
            END

       SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))

    END
IF ISNULL(@Splitter2, '') != ''
    BEGIN
       DECLARE @OStartLoop INT
       DECLARE @OEndLoop INT

       SELECT @OStartLoop = MIN(Id),
            @OEndLoop = MAX(Id)
       FROM @FResult

       WHILE @OStartLoop <= @OEndLoop
          BEGIN
             DECLARE @iString VARCHAR(4000)
             DECLARE @iMId INT

             SELECT @iString = SValue+@Splitter2,
                   @iMId = Id
             FROM @FResult
             WHERE Id = @OStartLoop

             WHILE CHARINDEX(@Splitter2, @iString) > 0
                BEGIN
                    DECLARE @iWorkingString VARCHAR(4000) = NULL

                    SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)

                    INSERT INTO @SResult
                    SELECT @iMId,
                         CASE
                         WHEN @iWorkingString = '' THEN NULL
                         ELSE @iWorkingString
                         END

                    SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))

                END

             SET @OStartLoop = @OStartLoop + 1
          END
       INSERT INTO @Result
       SELECT MId AS PrimarySplitID,
            ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
            SValue
       FROM @SResult
    END
ELSE
    BEGIN
       INSERT INTO @Result
       SELECT Id AS PrimarySplitID,
            NULL AS SecondarySplitID,
            SValue
       FROM @FResult
    END
RETURN

用法：

--FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)

--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')

可能的用法（获取每个拆分的第二个值）：

SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2

#19 楼

基于递归CTE的解决方案

declare @T table (iden int identity, col1 varchar(100));
insert into @T(col1) values
       ('ROOT/South America/Lima/Test/Test2')
     , ('ROOT/South America/Peru/Test/Test2')
     , ('ROOT//South America/Venuzuala ')
     , ('RtT/South America / ') 
     , ('ROOT/South Americas// '); 
declare @split char(1) = '/';
select @split as split;
with cte as 
(  select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end  as col1, 0 as pos                             , 1 as cnt
   from @T t
   union all 
   select t.iden, t.col1                                                                                                                              , charindex(@split, t.col1, t.pos + 1), cnt + 1 
   from cte t 
   where charindex(@split, t.col1, t.pos + 1) > 0 
)
select t1.*, t2.pos, t2.cnt
     , ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo
from cte t1 
join cte t2 
  on t2.iden = t1.iden 
 and t2.cnt  = t1.cnt+1
 and t2.pos > t1.pos 
order by t1.iden, t1.cnt;

#20 楼

出于对@AviG的所有应有的尊重，这是他配备的无错误版本的函数，用于完全返回所有令牌。

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TF' AND name = 'TF_SplitString')
DROP FUNCTION [dbo].[TF_SplitString]
GO

-- =============================================
-- Author:  AviG
-- Amendments:  Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe
-- Description: Tabel valued function that Breaks the delimeted string by given delimeter and returns a tabel having split results
-- Usage
-- select * from   [dbo].[TF_SplitString]('token1,token2,,,,,,,,token969',',')
-- 969 items should be returned
-- select * from   [dbo].[TF_SplitString]('4672978261,4672978255',',')
-- 2 items should be returned
-- =============================================
CREATE FUNCTION dbo.TF_SplitString 
( @stringToSplit VARCHAR(MAX) ,
  @delimeter char = ','
)
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

    DECLARE @name NVARCHAR(255)
    DECLARE @pos INT

    WHILE LEN(@stringToSplit) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(@delimeter, @stringToSplit)


        if @pos = 0
        BEGIN
            SELECT @pos = LEN(@stringToSplit)
            SELECT @name = SUBSTRING(@stringToSplit, 1, @pos)  
        END
        else 
        BEGIN
            SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
        END

        INSERT INTO @returnList 
        SELECT @name

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END

 RETURN
END

#21 楼

这是基于安迪·罗伯逊（Andy Robertson）的答案的，我需要除逗号之外的定界符。

 CREATE FUNCTION dbo.splitstring ( @stringToSplit nvarchar(MAX), @delim nvarchar(max))
RETURNS
 @returnList TABLE ([value] [nvarchar] (MAX))
AS
BEGIN

 DECLARE @value NVARCHAR(max)
 DECLARE @pos INT

 WHILE CHARINDEX(@delim, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@delim, @stringToSplit)  
  SELECT @value = SUBSTRING(@stringToSplit, 1, @pos - 1)

  INSERT INTO @returnList 
  SELECT @value

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + LEN(@delim), LEN(@stringToSplit) - @pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END
GO

并使用它：

 SELECT * FROM dbo.splitstring('test1 test2 test3', ' ');

（在SQL Server 2008 R2上测试） >
编辑：正确的测试代码

#22 楼

ALTER FUNCTION [dbo].func_split_string
(
    @input as varchar(max),
    @delimiter as varchar(10) = ";"

)
RETURNS @result TABLE
(
    id smallint identity(1,1),
    csv_value varchar(max) not null
)
AS
BEGIN
    DECLARE @pos AS INT;
    DECLARE @string AS VARCHAR(MAX) = '';

    WHILE LEN(@input) > 0
    BEGIN           
        SELECT @pos = CHARINDEX(@delimiter,@input);

        IF(@pos<=0)
            select @pos = len(@input)

        IF(@pos <> LEN(@input))
            SELECT @string = SUBSTRING(@input, 1, @pos-1);
        ELSE
            SELECT @string = SUBSTRING(@input, 1, @pos);

        INSERT INTO @result SELECT @string

        SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)       
    END
    RETURN  
END

#23 楼

您可以使用此功能：

        CREATE FUNCTION SplitString
        (    
           @Input NVARCHAR(MAX),
           @Character CHAR(1)
          )
            RETURNS @Output TABLE (
            Item NVARCHAR(1000)
          )
        AS
        BEGIN

      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

#24 楼

这是一个可以用作函数的示例，也可以在过程中添加相同的逻辑。
--SELECT * from [dbo] .fn_SplitString;

CREATE FUNCTION [dbo].[fn_SplitString]
(@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',')
       RETURNS @retTable TABLE 
(

    [value] VARCHAR(MAX) NULL
)AS

BEGIN

DECLARE
       @vCSV VARCHAR (MAX) = @CSV,
       @vDelimeter VARCHAR (100) = @Delimeter;

IF @vDelimeter = ';'
BEGIN
    SET @vCSV = REPLACE(@vCSV, ';', '~!~#~');
    SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~');
END;

SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '''', '&apos;'), '"', '&quot;');

DECLARE @xml XML;

SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>';

INSERT INTO @retTable
SELECT
       x.i.value('.', 'varchar(max)') AS COLUMNNAME
  FROM @xml.nodes('//i')AS x(i);

 RETURN;
END;

如果@vCSV包含禁止的字符，则此方法会中断...我刚刚发布了一个解决此问题的答案。

–Shnugo
17年2月2日在10:45

#25 楼

/ *

对T-SQL拆分字符串的答案
基于Andy Robinson和AviG的答案
增强的功能ref：LEN函数不包括SQL Server中的尾随空格
此“文件”应同时作为降价文件和SQL文件有效。


*/

    CREATE FUNCTION dbo.splitstring ( --CREATE OR ALTER
        @stringToSplit NVARCHAR(MAX)
    ) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX))
    AS BEGIN
        DECLARE @name NVARCHAR(MAX)
        DECLARE @pos BIGINT
        SET @stringToSplit = @stringToSplit + ','             -- this should allow entries that end with a `,` to have a blank value in that "column"
        WHILE ((LEN(@stringToSplit+'_') > 1)) BEGIN           -- `+'_'` gets around LEN trimming terminal spaces. See URL referenced above
            SET @pos = COALESCE(NULLIF(CHARINDEX(',', @stringToSplit),0),LEN(@stringToSplit+'_')) -- COALESCE grabs first non-null value
            SET @name = SUBSTRING(@stringToSplit, 1, @pos-1)  --MAX size of string of type nvarchar is 4000 
            SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned."
            INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added
            -- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @stringToSplit + '''(' + CAST(LEN(@stringToSplit+'_')-1 as nvarchar) + ')'
        END
        RETURN
    END
    GO

/*

测试案例：参见引用为“增强型”的URL “功能”上方

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,b')

Item | L
---  | ---
a    | 1
     | 0
b    | 1

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,')

Item | L   
---  | ---
a    | 1
     | 0
     | 0

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, ')

Item | L   
---  | ---
a    | 1
     | 0
     | 1

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, c ')

Item | L   
---  | ---
a    | 1
     | 0
 c   | 3

* /

回滚以纪念“此“文件”同时作为降价文件和SQL文件都应有效”

–mpag
19年8月12日在17:38

#26 楼

最简单的方法：

安装SQL Server 2016
使用STRING_SPLIT https://msdn.microsoft.com/zh-cn/library/mt684588.aspx

即使在速成版中也可以使用:)。

不要忘记将“兼容性级别”设置为SQL Server 2016（130）-在Management Studio中，右键单击数据库，属性/选项/兼容性级别。

– Tomino
16年11月9日在10:17

最初的帖子说的是SQL 2008 R2。安装SQL 2016可能不是一种选择

–肖恩·加维特（Shawn Gavett）
17年1月9日在17:34