问题描述
如何从 SQL Server 中的逗号分隔字符串获取值到行中,以便将它们插入表中?
How can I get values from a comma separated string in SQL Server in to rows, in order to insert them into a table?
例如,使用此数据:
Declare @string as nvarchar(max); Declare @substring as nvarchar(50); set @string = "Apple, Banana, Cherry, Orange, Mango"
我目前已经硬编码 set @last = 2,对于这个例子,但是 @last 应该包含字符串中的单词数.参数 @substring 将包含循环中的每个水果,我想用它来插入目标表.
I have currently hard-coded set @last = 2, for this example but @last should contain the number of words in the string. The parameter @substring will contain each fruit one by one in the loop, which I want to use to insert into a target table.
这是我当前的代码,但我不知道如何将 @last 设置为所需的值:
Here's my current code, but I'm stuck with how to set @last to the required value:
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 到目标表中:
You can do it all in one go rather than use a WHILE loop. So in this code, it will push the values into rows of a temp table, before using it to INSERT into a target table:
用于将逗号分隔值拆分为以下行的示例代码:
Sample code for splitting comma separated values to rows taken from:
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
此时,您在临时表中的行中有值,您可以使用它来填充目标表,如下所示:
At this point you have the values in rows in a temp table, which you can use to populate your target table like so:
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 小提琴演示
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 |