将字符串拆分成行的最有效方法

Question

我正在使用以下函数将字符串分割成行。这个函数比我之前使用的函数快得多，但是我需要更快地处理这些数据（这是一个ETL作业）：

ALTER FUNCTION [dbo].[ArrayToTable]
(
     @InputString VARCHAR(MAX) = ''
     , @Delimitter VARCHAR(1) = ',' 
) 
RETURNS @RESULT TABLE([Position] INT IDENTITY, [Value] VARCHAR(MAX)) 
AS 
BEGIN 
    DECLARE @XML XML 
    SELECT @XML = CONVERT(XML, SQL_TEXT) 
    FROM ( 
        SELECT '<root><item>' 
            + REPLACE(@InputString, @Delimitter, '</item><item>') 
            + '</item></root>' AS SQL_TEXT 
        ) dt 

    INSERT INTO @RESULT([Value]) 
    SELECT t.col.query('.').value('.', 'VARCHAR(1000)') AS [Value] 
    FROM @XML.nodes('root/item') t(col) 
    RETURN 
END 

有没有更好、更快的方法将分隔符字符串转换为行？我在查询中使用 cross apply 来连接这些结果。

有没有更高效的方法将分隔符字符串转换为行？

- Lock

Answer 1

这是我最高效的函数：

CREATE FUNCTION [Resource].[udf_SplitByXml]
      (@Data NVARCHAR(MAX), @Delimiter NVARCHAR(5))
RETURNS @Table TABLE 
    ( Data NVARCHAR(MAX)
    , SequentialOrder INT IDENTITY(1, 1))
AS
BEGIN

    DECLARE @TextXml XML;
    SELECT @TextXml = CAST('<d>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Data, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '"', '&quot;'), '''', '&apos;'), @Delimiter, '</d><d>') + '</d>' AS XML);

    INSERT INTO @Table (Data)
    SELECT Data = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(T.split.value('.', 'nvarchar(max)'))), '&amp;', '&'), '&lt;', '<'), '&gt;', '>'), '&quot;', '"'), '&apos;', '''')
    FROM @TextXml.nodes('/d') T(Split)

    RETURN
END

以下是您可以用来测试结果的示例调用：

SELECT * FROM Resource.udf_SplitByXml('yes, no, maybe, so', ',');
SELECT * FROM Resource.udf_SplitByXml('who|what|where|when|why|how|Uh, I don''t know!', '|');
SELECT * FROM Resource.udf_SplitByXml('Government, Education, Non-profit|Energy & Power|Yes|No', '|');
SELECT * FROM Resource.udf_SplitByXml('Energy & Power|Some<Thing>Wicked''This"Way Comes', '|');

另一种选择是尝试基于Adam Machanic的代码的CLR解决方案，该代码在这篇博客中赢得了性能测试的胜利。

- Registered User

---

对于CLR解决方案，我给出+1——对于大量字符串处理等任务，它是明显的赢家，并且非常容易实现。它还有一个额外的好处，可以打开一整套在SQL Server中通常无法访问的功能。 - Steve Pettifer

这很棒。唯一的问题是我不懂它。能否有人指引我去解释SELECT子句中T.split.value和FROM子句中T（Split）的MSDN文档？我好像找不到任何可以理解那个的东西。 - Dewald Swanepoel

Answer 2

STRING_SPLIT是SQL 2016+的新型开箱即用替代方案。

请参见: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16

没问题，执行计划建议使用它比其他方式更好，但实际上，它并不像自定义函数那样快; 顺便说一句，循环比IMO更少的开销更好。

这里有一个比较表明STRING_SPLIT效果最佳: https://sqlperformance.com/2016/03/sql-server-2016/string-split

- Dmitri Rechetilov

Answer 3

这是我刚写的另一个函数，令我惊讶的是，它比上面的XML方法略微快一点，尽管只是稍微快了一点。 将这两个函数与处理1,000行分隔字符串的函数进行比较，性能没有差异。处理50K行分隔字符串时，XML方法需要129秒才能生成435,217个提取的行，而我的简单字符串操作方法只需要122秒就可以生成提取的435,217行。

所以它并不真正快多少，尽管我猜如果你要处理数十万行数据，这会有所区别。我的函数的主要优点是易于阅读和理解，不依赖于可能在未来版本的SQL Server中更改的XML功能，并且应该很容易移植到任何语言。我真的希望能找到一些真正快速的东西，但我想现在这已经是我们能做到的最好的了。

  CREATE FUNCTION dbo.ufn_util_Split
     (
     @RawText varchar(max),
     @SplitCharacter varchar(2)
     )
  RETURNS @t_Results TABLE
     (
     RowIndex int IDENTITY(1,1),
     RowValue varchar(max)
     )

  AS

  BEGIN
     DECLARE @vc_RowValue varchar(max) = ''
     DECLARE @vc_Remainder varchar(max) = CASE WHEN RIGHT(@RawText,1) = @SplitCharacter THEN @RawText ELSE @RawText + @SplitCharacter END   --the string must end in the split character in order for this to work
     DECLARE @int_SplitPosition int = 0
     DECLARE @int_LenSplitChar int = 0
     SELECT @int_LenSplitChar = LEN(@SplitCharacter)

     --determine the first segment to start with
     SELECT @int_SplitPosition = CHARINDEX(@SplitCharacter,@vc_Remainder)
     WHILE (@int_SplitPosition > 0)
     BEGIN
        SELECT @vc_RowValue = LEFT(@vc_Remainder,@int_SplitPosition-1)
        INSERT INTO @t_Results (RowValue) VALUES (@vc_RowValue)

        --now strip off the segment we just extracted and determine where the next segment ends, and continue
        SET @vc_Remainder = SUBSTRING(@vc_Remainder,@int_SplitPosition+@int_LenSplitChar,LEN(@vc_Remainder))
        SELECT @int_SplitPosition = CHARINDEX(@SplitCharacter,@vc_Remainder)
        CONTINUE
     END
     RETURN
  END

- Todd Davis

Answer 4

你好，试试这个 -

create procedure sp_getAllItems 
@input varchar(100)
as
BEGIN

    create table #tmpFruits (name varchar(10))

    Declare @Qry Varchar(500)      
    Set @Qry = ''        
    Select @Qry = @Qry + ' Insert into #tmpFruits '    
    Select @Qry = @Qry +  Replace( 'Select ''' + 
    Replace(Replace(Replace(Replace(@input,CHAR(9),''),' 
    ',''),CHAR(10),''),CHAR(13),'') , ',',''' Union Select ''') + ''''           

    Exec (@Qry) 

    select * from Fruitstest where name in (select name from #tmpFruits)
    drop table #tmpFruits 

END

exec sp_getAllItems @input = 'cherry,banana'

- Santosh Paswan