Alternative to STRING_SPLIT function, to split comma-joined values ​​to rows?

There are several alternatives, and all have their complexity.

1. XML

In the first part, each of the items is separated, doing a replace to convert the separator into a varchar that contains the data and between them we see the xml elements.

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);

Then, since it is already an xml, it is queried using xQuery.

Disadvantages: The data source cannot contain "<>", since it would return an XML Parsing error. Invalid character in full name.

2. DelimitedSplit8K

There is a lot of controversy in specialized forums with this function created by Jeff Moden, about whether its performance analyzes are exaggerated or prepared for an execution in which it benefits, but what is certain is that it works very well and is very efficient. .

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
;

now to use it

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

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

Limited Split 8k

3. Combination of functions.

We will now use a combination of two "master" functions. The first created by Itzik Ben-Gan , is very usable for many other things, it returns a column of the numbers that are among those defined as parameters.

----------------------------------------------------------------------
-- © 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

And the second, that I cannot put the link of its author, basically because I do not know him, but it is based on this first one.

-- 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
);

Now with the two table-type functions, their consumption would be:

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

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

Summary:

Although there are other ways, these three are very good in performance, don't use cursors, aren't limited to a finite number of elements, and aren't limited to scalar values ​​either. All three allow you to treat columns as sets with the apply operator.

Additional note: That you don't want to use functions, for some specific reason, doesn't mean that you can't reform a function to use the same code directly in your developments.

--Another option, although very caveman but another option after all:

/* Function to replace the use of 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

*/

Create Or Alter Function dbo.fn_String_Split( @ValueDatos VarChar(512) ) Returns @TablaDatos Table (Value VarChar(512))

ACE

Begin Declare @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

End