問題描述

如何從 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?

例如，使用此數(shù)據(jù):

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

我目前已經(jīng)硬編碼 set @last = 2，對于這個(gè)例子，但是 @last 應(yīng)該包含字符串中的單詞數(shù).參數(shù) @substring 將包含循環(huán)中的每個(gè)水果，我想用它來插入目標(biāo)表.

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.

這是我當(dāng)前的代碼，但我不知道如何將 @last 設(shè)置為所需的值:

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 循環(huán).因此，在此代碼中，它會(huì)將值推送到臨時(shí)表的行中，然后使用它INSERT 到目標(biāo)表中:

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

此時(shí)，您在臨時(shí)表中的行中有值，您可以使用它來填充目標(biāo)表，如下所示:

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

結(jié)果:

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

這篇關(guān)于將逗號分隔的值拆分為行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！