gpt4 book ai didi

sql-server - 将逗号分隔值拆分为行

转载 作者:行者123 更新时间:2023-12-01 12:39:07 33 4
gpt4 key购买 nike

如何从 SQL Server 中的逗号分隔字符串中获取值到行中,以便将它们插入到表中?

例如,使用此数据:

 Declare @string as nvarchar(max);
Declare @substring as nvarchar(50);
set @string = "Apple, Banana, Cherry, Orange, Mango"

对于这个例子,我目前硬编码了 set @last = 2,但是 @last 应该包含字符串中的单词数。参数 @substring 将在循环中一个接一个地包含每个水果,我想用它插入到目标表中。

这是我当前的代码,但我对如何将 @last 设置为所需的值感到困惑:

DECLARE @first AS INT
SET @first = 1
DECLARE @step AS INT
SET @step = 1
DECLARE @last AS INT
SET @last = 2

BEGIN TRANSACTION
WHILE(@first <= @last)
BEGIN

INSERT INTO tbFruit(Name)
VALUES(@substring);

SET @first += @step
END
COMMIT TRANSACTION

最佳答案

您可以一次性完成所有工作,而不是使用 WHILE 循环。所以在这段代码中,它会将值推送到临时表的行中,然后再将其用于 INSERT 到目标表中:

将逗号分隔值拆分为从以下位置获取的行的示例代码:

Convert Comma Separated column value to rows

DECLARE @string AS NVARCHAR(MAX);
DECLARE @substring AS NVARCHAR(50);
SET @string = 'Apple, Banana, Cherry, Orange, Mango'

SELECT Split.a.value('.', 'VARCHAR(100)') AS Fruits
INTO #fruits
FROM
(
SELECT CAST ('<M>' + REPLACE(@string, ', ', '</M><M>') + '</M>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/M') AS Split ( a );

-- show what's in the temp table
SELECT *
FROM #fruits

此时,您在临时表中的行中有了值,您可以使用它来填充目标表,如下所示:

INSERT INTO tbFruit ( Name )
SELECT Fruits FROM #fruits

-- show what's in the target table
SELECT * FROM #target_table

-- tidy up
DROP TABLE #fruits

SQL Fiddle Demo

TSQL代码:

 DECLARE @string AS NVARCHAR(MAX) = 'Apple, Banana, Cherry, Orange, Mango'
DECLARE @substring AS NVARCHAR(50)

SELECT Split.a.value('.', 'VARCHAR(100)') AS Fruits
INTO #fruits
FROM ( SELECT CAST ('<M>' + REPLACE(@string, ', ', '</M><M>') + '</M>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/M') AS Split ( a )

CREATE TABLE #target_table ( Fruits NVARCHAR(50) )

INSERT INTO #target_table
( fruits )
SELECT *
FROM #fruits

SELECT * FROM #target_table

DROP TABLE #fruits
DROP TABLE #target_table

结果:

| FRUITS |
|--------|
| Apple |
| Banana |
| Cherry |
| Orange |
| Mango |

关于sql-server - 将逗号分隔值拆分为行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26755165/

33 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com