SQL double split on delimiter

Question

所以我将字符串值发送到数据库

@string = 'Value1|Value2|Value3|Value4, OtherVal1|OtherVal2|OtherVal3|OtherVal4'

还有一些其他的id

@id = '1'

我想先在 ',' 上拆分它，然后在 | 上拆分它并插入到表中

例子

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

这是我到目前为止完成的代码，但我的另一个 while 函数按值返回值，

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

当前代码通过两个 while 语句并按其分隔符拆分代码，

知道如何从中插入表格。

Answer 1

这是使用 PARSENAME 的一个技巧

首先使用任何拆分字符串方法拆分值。我使用 XML 方法拆分字符串

然后使用PARSENAME 函数将值拆分为列的值

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 

在 SQL Server 2016 中，您可以使用 STRING_SPLIT 函数拆分数据

;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 

结果:

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