如何分割字符串,并保存到T-SQL中的数组 [英] How to split string and save into an array in T-SQL
问题描述
我写一个游标来填充来自主表的新表中的数据,其中包含以下方式的数据
I am writing a cursor to populate data in new table from main table which contains data in below manner
项目  颜色
衬衫  红,蓝,绿,黄
Item Colors
Shirt Red,Blue,Green,Yellow
我想通过获取该项目,然后行添加它来填充新表的数据,根据它包含每个颜色
I want to populate new Table data by fetching the Item and then adding it in row, according to each color it contains
项目  颜色
衬衫  红
衬衫  蓝
衬衫  绿色
衬衫  黄
Item Color
Shirt Red
Shirt Blue
Shirt Green
Shirt Yellow
我被困在如何
1)Delimate /拆分颜色的字符串
2)要保存它在一个数组
3)在光标使用它
1) Delimate/Split "Colors" string 2) To save it in an array 3) To use it in cursor
因为我要使用嵌套游标用于这一目的。
as I am going to use Nested cursor for this purpose.
推荐答案
使用SQL Server 2005+和XML数据类型,你可以看看下面的
Using Sql Server 2005+ and the XML datatype, you can have a look at the following
DECLARE @Table TABLE(
Item VARCHAR(250),
Colors VARCHAR(250)
)
INSERT INTO @Table SELECT 'Shirt','Red,Blue,Green,Yellow'
INSERT INTO @Table SELECT 'Pants','Black,White'
;WITH Vals AS (
SELECT Item,
CAST('<d>' + REPLACE(Colors, ',', '</d><d>') + '</d>' AS XML) XmlColumn
FROM @Table
)
SELECT Vals.Item,
C.value('.','varchar(max)') ColumnValue
FROM Vals
CROSS APPLY Vals.XmlColumn.nodes('/d') AS T(C)
这篇关于如何分割字符串,并保存到T-SQL中的数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!