Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
845 views
in Technique[技术] by (71.8m points)

tsql - 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

I am stuck in how to

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...