if OBJECT_ID('test') is not null
drop table test
go
create table test
(
id int,
[name] varchar(10),
[key] varchar(20)
)
go
insert test
select 1,'lisa','lia,is,sa' union all
select 2,'sophia','abc,cd,ef' union all
select 3,'lori','啊12,34,23'
go
select * from test
id name key
----------- ---------- --------------------
1 lisa lia,is,sa
2 sophia abc,cd,ef
3 lori 啊12,34,23
(3 行受影响)
如上代码所示,现要将test中的数据变为下面数据:
id name key
----------- ---------- --------------------
1 lisa lia
1 lisa is
1 lisa sa
2 sophia abc
2 sophia cd
2 sophia ef
3 lori 啊12
3 lori 34
3 lori 23
(9 行受影响)
即把key列中的字符串按“,”进行拆分成行数据。
执行查询如下:
select
id,
a.[name],
SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
from
test a,master..spt_values
where
number >=1 and number<=len([key])
and [type]='p'
and substring(','+[key],number,1)=','
id name key
----------- ---------- --------------------
1 lisa lia
1 lisa is
1 lisa sa
2 sophia abc
2 sophia cd
2 sophia ef
3 lori 啊12
3 lori 34
3 lori 23
(9 行受影响)
解释:
①、CHARINDEX(',',[key]+',',number):number 是master..spt_values 表中取得的自然数字(1、2、3......);CHARINDEX(',',[key]+',',number)是指key列加‘,’字符串中从第number个字符开始,‘,’的位置;返回int类型。
eg.
select CHARINDEX(',','lia,is,sa'+',',1)
-----------
4
(1 行受影响)
②、SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number):从key列的第number个位置开始取CHARINDEX(',',[key]+',',number)-number个字符。
eg.
select SUBSTRING('lia,is,sa',1,CHARINDEX(',','lia,is,sa'+',',1)-1)--CHARINDEX(',','lia,is,sa'+',',1)=4
------
lia
(1 行受影响)