STRING_SPLIT en SQL Server 2012

No dejes de divulgar nuestro espacio y códigos en tus redes sociales, necesitamos tu ayuda para ampliar nuestra comunidad.

Solución:

Otro enfoque es demasiado útil XML Método con CROSS APPLY para dividir sus datos separados por comas:

SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
    SELECT CAST(''+REPLACE(@ID, ',', '')+'' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a);

Resultado :

DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Ejemplo :

DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id    NVARCHAR(300),
 marks NVARCHAR(300)
); 
--insert into @StudentsMark 
;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST(''+REPLACE(@ID, ',', '')+'' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST(''+REPLACE(@Marks, ',', '')+'' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a))
     INSERT INTO @StudentsMark
            SELECT C.id,
                   C1.marks
            FROM CTE C
                 LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
SELECT *
FROM @StudentsMark;

La función en línea basada en Yogesh Sharma y Salman A responde:

Create FUNCTION [dbo].[fn_split_string]
(
    @string    nvarchar(max),
    @delimiter nvarchar(max)
)
/*
    The same as STRING_SPLIT for compatibility level < 130
    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
*/
RETURNS TABLE AS RETURN
(
    SELECT 
      --ROW_NUMBER ( ) over(order by (select 0))                            AS id     --  intuitive, but not correect
        Split.a.value('let $n := . return count(../*[. << $n]) + 1', 'int') AS id
      , Split.a.value('.', 'NVARCHAR(MAX)')                                 AS value
    FROM
    (
        SELECT CAST(''+REPLACE(@string, @delimiter, '')+'' AS XML) AS String
    ) AS a
    CROSS APPLY String.nodes('/X') AS Split(a)
)

Ejemplo:

DECLARE @ID NVARCHAR(300)= 'abc,d,e,f,g';
select * from fn_split_string(@ID,',')

-- If you need exactly string_split functionality (without id column):
select value from fn_split_string(@ID,',')

Otro enfoque sería usar CHARINDEX y SUBSTRING en un MIENTRAS:

DECLARE @IDs VARCHAR(500);
DECLARE @Number VARCHAR(500);
DECLARE @charSpliter CHAR;

SET @charSpliter = ',';
SET @IDs = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' + @charSpliter;

WHILE CHARINDEX(@charSpliter, @IDs) > 0
BEGIN
    SET @Number = SUBSTRING(@IDs, 0, CHARINDEX(@charSpliter, @IDs));
    SET @IDs = SUBSTRING(@IDs, CHARINDEX(@charSpliter, @IDs) + 1, LEN(@IDs));

    PRINT @Number;

END;

Si sostienes algún reparo o disposición de arreglar nuestro artículo te mencionamos dejar una aclaración y con placer lo analizaremos.

---

---

Utiliza Nuestro Buscador