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. sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange
– Sean Lange, Commented Sep 26, 2016 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: sqlservercentral.com/articles/SQL+Server+2012/106903 — Alan Burstein
– Alan Burstein, Commented Sep 26, 2016 at 22:47

John Cappelletti · Accepted Answer · 2016-09-26 22:03:05Z

2

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

edited Sep 26, 2016 at 22:03

answered Sep 26, 2016 at 21:34

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Irawan Soetomo Over a year ago

Really love your super fast parser here, stackoverflow.com/questions/39650912/….

Collectives™ on Stack Overflow

Split string in T-SQL and inserting into parameter

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related