Split_String in SQL and use its values

3

I have a very simple operation, but I do not know how to proceed. In a Stored Procedure I get a string full of Id's separated by commas, so I need to separate that string and use its content within a INSERT . But I do not know how to use each part of said split this is what I have:

select * from string_split(@listaDePagosErrores, ',')
  --ahora quiero usar los splits en un insert

INSERT INTO[dbo].[ProgramacionIngresos]
  (IdError)
VALUES
  (split[1]) -- ? ? ? ? o algo así ?
  SELECT * FROM [ProgramacionIngresos]
    
asked by E.Rawrdríguez.Ophanim 31.08.2018 в 01:28
source

3 answers

2

As I mentioned in the comments. You will not be able to use the STRING_SPLIT function since it was introduced as of version 13 (SQL Server 2016) and you have version 11.

You'll have to build your own function. In this answer from Stack Overflow there is a feature that can help you just for what you want:

CREATE FUNCTION Split (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
      Item                          VARCHAR(8000)
)

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item           VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)[email protected])
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

To use the function, it would be:

SELECT * FROM Split(@ListaPagosErrores)

Up to this point you only have one table and each record separated by the value declared in the function. To make the INSERT from a SELECT would be as follows:

INSERT INTO[dbo].[ProgramacionIngresos]
    (IdError)
VALUES
    SELECT Item AS IdError FROM Split(@ListaPagosErrores)
    
answered by 31.08.2018 / 16:51
source
1

There are many ways to split a chain; I suggest that within a CURSOR you use a function to make the split and store the values in a TABLE, then you do a WHILE to the cursor and inside it you make INSERT'S until the loop is finished.

Here are some examples:

Here is the FUNCTION:

CREATE FUNCTION [ufn_SplitStrings] ( 
    @stringToSplit VARCHAR(MAX)
)
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS

BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)[email protected])
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

Here is the CURSOR:

DECLARE myCursor CURSOR FOR
    --Acá llamas a la Función que hace el split
    SELECT Name
    FROM [ufn_SplitStrings](@p_cadenaParaSplit)
    WHERE Name <> '';

Here you open and close the CURSOR to do the WHILE:

DECLARE @codigoErrorINT

    OPEN myCursor 
        FETCH NEXT FROM myCursor INTO @codigoError;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Acá realizas tu insert's 
            INSERT INTO[dbo].[ProgramacionIngresos](IdError)
                 VALUES (@codigoError)

            FETCH NEXT FROM myCursor INTO @codigoError
        END

    CLOSE myCursor 
    DEALLOCATE myCursor 

You try and tell me how it went.

    
answered by 31.08.2018 в 03:09
1

The string_split function is available as of the 2016 version of SQL Server. That said, its implementation would be as follows:

declare @ListaPagosErrores varchar(500) = '1,3,4,66,62,101'

declare @ProgramacionIngresos table(IDError int)


insert into @ProgramacionIngresos
select [Value]
from string_split(@ListaPagosErrores, ',')



select *
from @ProgramacionIngresos
    
answered by 31.08.2018 в 15:57