Q:

Sql double split on delimiter

So i am sending string value to database
@string = 'Value1|Value2|Value3|Value4, OtherVal1|OtherVal2|OtherVal3|OtherVal4'

And some other id 
@id = '1'
And i want to split it first on ',' then split it on | and insert into table 
Example
TABLE
ID     | COLUMN1    |COLUMN2      |COLUMN3      |COLUMN4        |COLUMN5
ai     |  @id       | value1      |value2       |value3         |value4
ai     |  @id2      |otherVal1    |otherVal2    |otherVal3      |otherVal4

This is my code i have done so far , but my other while function returns value by value,
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 <> 0)
BEGIN
    SET @IDSet = substring(@string,1,@pos -1) + @delimiter2
    --SELECT @IDSet

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

        WHILE(@columnsPos <> 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

Current code goes through two while statements and splits code by its delimiter,
Any idea how to make insert into table out of it . 

A:

Here is one trick using PARSENAME
First split the values using any split string methods.  I have used XML method  split the string 
Then use PARSENAME function to split the values to column's
DECLARE @string VARCHAR(500)= 
        'Value1|Value2|Value3|Value4, OtherVal1|OtherVal2|OtherVal3|OtherVal4'; 

WITH cte 
     AS (SELECT Replace(Rtrim(Ltrim(split.a.value('.', ' VARCHAR(100)'))), '|','.')  AS split_data 
         FROM   (SELECT Cast ('<M>' + Replace(@string, ',', '</M><M>') 
                              + '</M>' AS XML) AS Data) AS A 
                CROSS apply data.nodes ('/M') AS Split(a)) 
SELECT COLUMN2= Parsename(split_data, 4), 
       COLUMN3=Parsename(split_data, 3), 
       COLUMN4= Parsename(split_data, 2), 
       COLUMN5=Parsename(split_data, 1) 
FROM   cte 

In SQL Server 2016 you can use STRING_SPLIT function the split the data
;WITH cte 
     AS (SELECT split_data = Replace(Rtrim(Ltrim(value)), '|', '.')  
         FROM   String_split(@string, ',')) 
SELECT COLUMN2= Parsename(split_data, 4), 
       COLUMN3=Parsename(split_data, 3), 
       COLUMN4= Parsename(split_data, 2), 
       COLUMN5=Parsename(split_data, 1) 
FROM   cte 

Result : 
╔═══════════╦═══════════╦═══════════╦═══════════╗
║  COLUMN2  ║  COLUMN3  ║  COLUMN4  ║  COLUMN5  ║
╠═══════════╬═══════════╬═══════════╬═══════════╣
║ Value1    ║ Value2    ║ Value3    ║ Value4    ║
║ OtherVal1 ║ OtherVal2 ║ OtherVal3 ║ OtherVal4 ║
╚═══════════╩═══════════╩═══════════╩═══════════╝

