Sql double split on delimiter
<p>So i am sending string value to database</p>

<pre><code>@string = 'Value1|Value2|Value3|Value4, OtherVal1|OtherVal2|OtherVal3|OtherVal4'
</code></pre>

<p>And some other id </p>

<p>@id = '1'</p>

<p>And i want to split it first on ',' then split it on | and insert into table </p>

<p>Example</p>

<pre><code>TABLE
ID     | COLUMN1    |COLUMN2      |COLUMN3      |COLUMN4        |COLUMN5
ai     |  @id       | value1      |value2       |value3         |value4
ai     |  @id2      |otherVal1    |otherVal2    |otherVal3      |otherVal4
</code></pre>

<p>This is my code i have done so far , but my other while function returns value by value,</p>

<pre><code>DECLARE @string nvarchar(max)
DECLARE @IDSet nvarchar(max)

DECLARE @columnsValue nvarchar(max)
DECLARE @columnsPos int

DECLARE @pos int
DECLARE @nextPost int

DECLARE @delimiter nchar(1)
DECLARE @delimiter2 nchar(1)



SET @delimiter2 = '|'
SET @delimiter = ','

SET @string = 'ColumnName|autocompleteId|autocompleteValue|AndOrStatus,ColumnName1|autocompleteId1|autocompleteValue1|AndOrStatus1'  + @delimiter

SET @pos = charindex(@delimiter, @string)


WHILE(@pos &lt;&gt; 0)
BEGIN
    SET @IDSet = substring(@string,1,@pos -1) + @delimiter2
    --SELECT @IDSet

        SET @columnsPos = charindex(@delimiter2,@IDSet)

        WHILE(@columnsPos &lt;&gt; 0)
        BEGIN
            SET @columnsValue = substring(@IDSet ,1,@columnsPos - 1)
            SELECT @columnsValue
            SET @IDSet = substring(@IDSet,@columnsPos +1, LEN(@IDSet))
            SET @columnsPos = charindex(@delimiter2,@IDSet)
        END

    SET @string = substring(@string,@pos + 1,LEN(@string))
    SET @pos = charindex(@delimiter,@string)
    END
</code></pre>

<p>Current code goes through two while statements and splits code by its delimiter,</p>

<p>Any idea how to make insert into table out of it .</p>