Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
844 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 Answer

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...