create function jiadh(@str nvarchar(50))
returns nvarchar(100)
as
begin
declare @str nvarchar(50)
declare @Nstr nvarchar(1000)
set @Nstr=''
while len(@str)>0
begin
set @Nstr=@Nstr+left(@str,1)+','
set @str=right(@str,len(@str)-1)
end
set @Nstr=left(@Nstr,len(@Nstr)-1)
return @Nstr
end
declare @tmp table(m varchar(10))
declare @outTmp table(m varchar(20))
declare @str varchar(100)
declare @outStr varchar(150)
declare @i int
set @outStr=''
insert into @tmp values('abcde')
insert into @tmp values('ccedm')
select * from @tmp
declare cur1 cursor for
select m from @tmp
open cur1
fetch next from cur1 into @str
while @@fetch_status=0
begin
set @i=1
set @outstr=''
while @i<=len(@str)
begin
set @outstr=@outstr+substring(@str,@i,1)+','
set @i=@i+1
end
fetch next from cur1 into @str
insert into @outTmp values(left(@outstr,len(@outstr)-1))
end
select * from @outTmp
deallocate cur1
其实这些可以用程序来做,读取数据后在程序把字符分一个一个取出来放入数组.可能效率更好一点.
结果:
m
--------------------
a,b,c,d,e
c,c,e,d,m
CREATE PROCEDURE aaa AS
create function jiadh(@str nvarchar(50))
returns nvarchar(100)
as
begin
declare @str nvarchar(50)
declare @Nstr nvarchar(1000)
set @Nstr=''
while len(@str)>0
begin
set @Nstr=@Nstr+left(@str,1)+','
set @str=right(@str,len(@str)-1)
end
set @Nstr=left(@Nstr,len(@Nstr)-1)
return @Nstr
end
--可以写成一个自定义函数
create function jiadh(@str nvarchar(50))
returns nvarchar(100)
as
begin
declare @Nstr nvarchar(1000)
set @Nstr=''
while len(@str)>0
begin
set @Nstr=@Nstr+left(@str,1)+','
set @str=right(@str,len(@str)-1)
end
set @Nstr=left(@Nstr,len(@Nstr)-1)
return @Nstr
end
declare @str nvarchar(50)
set @str='abcdefg'
declare @Nstr nvarchar(1000)
set @Nstr=''
while len(@str)>0
begin
set @Nstr=@Nstr+left(@str,1)+','
set @str=right(@str,len(@str)-1)
end
set @Nstr=left(@Nstr,len(@Nstr)-1)
select @Nstr