create function aa12(@s varchar(50)) returns varchar(50) as begin return replace(''''+@s+'''',',',''',''') endselect dbo.aa12('OP0001,OP0002,OP0003')/* 'OP0001','OP0002','OP0003' */
create function f_str(@str varchar(1000),@split varchar(20)) returns @t table(code varchar(30)) as begin while charindex(@split,@str)>0 begin insert into @t select left(@str,charindex(@split,@str)-1) set @str=stuff(@str,1,charindex(@split,@str)+len(@split)-1,'') end insert into @t select @str return end goselect * from dbo.f_str('OP0001,OP0002,OP0003',',') go/* code ------------------------------ OP0001 OP0002 OP0003 */drop function f_str go
create function aa12(@s varchar(50))
returns varchar(50)
as
begin return replace(''''+@s+'''',',',''',''')
endselect dbo.aa12('OP0001,OP0002,OP0003')/*
'OP0001','OP0002','OP0003'
*/
create function f_str(@str varchar(1000),@split varchar(20))
returns @t table(code varchar(30))
as
begin
while charindex(@split,@str)>0
begin
insert into @t select left(@str,charindex(@split,@str)-1)
set @str=stuff(@str,1,charindex(@split,@str)+len(@split)-1,'')
end
insert into @t select @str
return
end
goselect * from dbo.f_str('OP0001,OP0002,OP0003',',')
go/*
code
------------------------------
OP0001
OP0002
OP0003
*/drop function f_str
go