Split string in T-SQL and inserting into parameter

Question

I need to split a string by delimiters |, then for every value obtained, I need to insert same in the name field like so:

INSERT INTO Monitoring (UserId, Name, DateCreated)
VALUES (@UserId, 'abc', getdate())
VALUES (@UserId, 'def', getdate()) etc...

Below is my code to split the string.

DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'abc|def|gh|ijj'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1

SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)

WHILE @SplitEndPos > 0
BEGIN
    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))

    INSERT @SplitStringTable (Value) VALUES (@SplitValue)

    SET @SplitStartPos = @SplitEndPos + 1
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END

BEGIN TRANSACTION T1

    DECLARE @i int = 0

    WHILE @i < @SplitEndPos
    BEGIN
        INSERT INTO Monitoring (UserId, Name, DateCreated)
        VALUES (@UserId, @Name, getdate())

        SET @i = @i + 1
    END

    COMMIT TRANSACTION T1

Any help please, how shall I proceed?

There is no need to resort to looping for splitting strings. Please see this article and replace your string splitter. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Sep 26 '16 at 21:28
Ditto what Sean said, the link he posted is to an article that describes the fastest way to split a string on a pre-2012 system. If you're running SQL Server 2012+ here's a slightly faster version that leverages the LEAD analytic function: http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/ — Alan Burstein, Sep 26 '16 at 22:47

John Cappelletti · Answer 1 · 2016-09-26T22:03:05.193

There are many split/parsing functions out there.

Assume variables:

Declare @UserID int = 1
Declare @String varchar(max)='abc|def'

Insert Into Monitoring (UserId,Name,DateCreated)
Select UserID      = @UserID
      ,Name        = Key_Value
      ,DateCreated = GetDate() 
 From [dbo].[udf-Str-Parse](@String,'|')

The following would be inserted

UserID  Name    DateCreated
1       abc     2016-09-26 17:31:24.107
1       def     2016-09-26 17:31:24.107

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As

Return
(  
    Select Key_PS    = Row_Number() over (Order By (Select null))
          ,Key_Value = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);

EDIT

By the way, if you just run the parse function alone, you would get the following:

    Declare @String varchar(max)='abc|def'
    Select * From [dbo].[udf-Str-Parse](@String,'|')

-- Returns

    Key_PS  Key_Value
    1       abc
    2       def

Really love your super fast parser here, http://stackoverflow.com/questions/39650912/how-to-pass-an-array-of-integer-values-from-a-table-to-a-stored-procedure/39651231#39651231. — Irawan Soetomo, Sep 28 '16 at 05:42

Split string in T-SQL and inserting into parameter

1 Answers1