SQL Server 函数学习笔记⑥函数的高级应用=》按规则拆分列形成行数据

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 行受影响)