替代 STRING_SPLIT 函数，将逗号连接的值拆分为行？

有几种选择，并且都有其复杂性。

1. XML

在第一部分中，每个项目都是分开的，执行替换以将分隔符转换为包含数据的 varchar，在它们之间我们看到 xml 元素。

DECLARE @ValueDatos varchar(512) = 'Uno, Dos, Tres';
Declare @splitSeparator varchar(1) = ',';

DECLARE @X XML;
   SELECT @X = CONVERT(XML, '<r><s>' 
       + REPLACE(@ValueDatos, @splitSeparator, '</s><s>') + '</s></r>');

SELECT LTRIM(RTRIM((T.c.value('.', 'varchar(max)')))) AS Value
     FROM @X.nodes('/r/s') T(c);

然后，由于它已经是一个 xml，因此使用 xQuery 对其进行查询。

缺点：数据源不能包含“<>”，因为它会返回一个 XML Parsing 错误。全名中的字符无效。

2. 定界Split8K

Jeff Moden 创建的这个功能在专门的论坛上存在很多争议，关于它的性能分析是否被夸大或为它受益的执行做好了准备，但可以肯定的是它运行得非常好并且非常高效。 。

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

现在使用它

DECLARE @ValueDatos varchar(512) = 'Uno, Dos, Tres';
Declare @splitSeparator varchar(1) = ',';

Select * from
    dbo.[DelimitedSplit8K](@ValueDatos, @splitSeparator);

有限拆分 8k

3. 功能组合。

我们现在将使用两个“主”功能的组合。第一个由Itzik Ben-Gan创建，对许多其他事情非常有用，它返回一列定义为参数的数字。

----------------------------------------------------------------------
-- © Itzik Ben-Gan, SolidQ
-- For more, see 5-day Advanced T-SQL Course:
-- http://tsql.solidq.com/t-sql-courses/
----------------------------------------------------------------------

IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
             FROM L5)
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum;
GO

第二个，我不能放它的作者的链接，主要是因为我不认识他，但它是基于第一个。

-- Erland's split_me
Create Function [dbo].[ufn_inline_split_me]
(
@param nvarchar(MAX), 
@delimiter nchar(1) = N','
)
RETURNS table AS
RETURN (
SELECT
    ROW_NUMBER() OVER(ORDER BY n) AS pos,
    LTRIM(RTRIM(CONVERT(nvarchar(4000),
    SUBSTRING(@param, n,
    CHARINDEX(@delimiter, 
        @param + CONVERT(nvarchar(MAX), 
    @delimiter), n) - n)))) 
    AS Value
FROM
    dbo.GetNums(1, LEN(@param))
WHERE
    SUBSTRING(CONVERT(nvarchar(MAX), @delimiter) 
   + @param, n, 1) 
    = @delimiter
);

现在有了两个表类型函数，它们的消耗将是：

DECLARE @ValueDatos varchar(512) = 'Uno, Dos, Tres';
Declare @splitSeparator varchar(1) = ',';

Select * from
    dbo.[ufn_inline_split_me](@ValueDatos, @splitSeparator);

概括：

虽然还有其他方式，但是这三种在性能上都很好，不使用游标，不限于有限个元素，也不限于标量值。这三个都允许您使用 apply 运算符将列视为集合。

附加说明：出于某种特定原因，您不想使用函数并不意味着您不能在开发中直接使用相同的代码来改造函数。

--另一种选择，虽然非常原始但毕竟是另一种选择：

/* 使用String_Split替换函数(With SqlServer 2019)

Ejemplo de ejecución:

    Select * from dbo.fn_String_Split('Uno, Dos, Tres')
    
    ...y debe regresar algo como la siguiente tabla:

            Valor
            ---------       <--Sin ésto, claro!
            Uno
            Dos
            Tres

*/

创建或更改函数 dbo.fn_String_Split(@ValueDatos VarChar(512)) 返回 @TablaDatos 表 (Value VarChar(512))

高手

开始声明 @Delimiter Char(1) = ',',
@From Int,
@To Int, @Value VarChar(512)

While LEN(@ValueDatos) > 0
    Begin
        Set @Hasta = CHARINDEX(@Delimitador,@ValueDatos)
        If @Hasta > 0
            Begin
                /* Cuando hay delimitadores */
                Set @Valor = Substring(@ValueDatos, 1, @Hasta - 1)              /* Extrae el valor hasta un caracter antes de donde está el delimitador */
                Set @Desde = @Hasta + 1
                Set @ValueDatos = LTrim(SUBSTRING(@ValueDatos, @Desde, 512 ))   /* Conserva el string remanente sin procesar. Si quedan menos de 512 caracteres no hay problema */
            End
        Else
            Begin
                /* Cuando ya no haya delimitadores, se toma el último valor */
                Set @Valor = @ValueDatos                                        /* Toma el último valor ya que no hay delimitadores a la derecha */
                Set @ValueDatos = ''                                            /* Elimina los valores restantes para terminar el ciclo (While) */
            End

        /* Guarda el valor en un registro de la tabla */
        Insert Into @TablaDatos Values (@Valor)
    End

Return

结尾