sql-server - 将字符串拆分为行的最有效方法

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 来加入这些结果。

谁能想到一种更有效的方法来将分隔字符串转换为行？

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 解决方案，该代码是性能测试的赢家 in this blog .