Question

sql 如何以逗号为分隔符分割一个字段的值

写了个sql分割，感觉写的超级复杂。有没有简单的方法？toad不支持split函数。。。。
SELECT osys.DOWNLOADFLOWLIST, SUBSTR (osys.DOWNLOADFLOWLIST, 0, INSTR (osys.DOWNLOADFLOWLIST, ',', 1) -1) AS time1,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,2) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 1)-1) AS time2,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 2)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,3) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 2)-1) AS time3,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 3)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,4) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 3)-1) AS time4,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 4)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,5) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 4)-1) AS time5,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 5)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,6) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 5)-1) AS time6,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 6)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,7) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 6)-1) AS time7,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 7)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,8) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 7)-1) AS time8,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 8)+ 1, INSTR (osys.DOWNLOADFLOWLIST, ',', 1,9) -INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 8)-1) AS time9,SUBSTR (osys.DOWNLOADFLOWLIST, INSTR (osys.DOWNLOADFLOWLIST, ',', 1, 9)+ 1 ) AS time10 FROM OTT_STB_VIDEO_ALARM osva INNER JOIN ott_systeminfo osysON osys.id = osva.SYSTEMINFO_ID WHERE osva.alarm_id = 3950;

Answer 1

可用substring函数。

创建测试表及数据：

create table test
(id varchar(10));

insert into test values ('123abc');
insert into test values ('456def');
insert into test values ('789ghi');

执行：

select substring(id,1,3) +','+substring(id,4,3) as id from test

结果截图：

也就显示成了用逗号分隔的样子。

Answer 2

create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin

while(charindex(@split,@c)<>0)
begin

insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
go

select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')

Answer 3

create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin

while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
go

select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')

Answer 4

用charindex