比如表A,就只有一个字段b,值为1234(文本类型) 我想要段sql,就是select b from A。。后的结果为1,2,3,4 结果集要用逗号隔开。
alter function fn_getStr(@v int) returns varchar(32) begin declare @retValue varchar(32) set @retValue ='' if (@v is not null)
begin
declare @s varchar(16),
@l tinyint, @i tinyint set @i = 1 set @s =convert(varchar(16),@v) set @l = len(@s) while(@i<=@l) begin set @retValue = @retValue + substring(@s,@i,1)+',' --print @retValue set @i = @i + 1 end end return left(@retValue,len(@retValue)-1) endselect dbo.fn_getStr(123)-------------------------------- 1,2,3,4(1 行受影响)
--> 测试数据: #tb if object_id('tempdb.dbo.#tb') is not null drop table #tb create table #tb ([val] varchar(10)) set nocount on insert into #tb select '1234'declare @Num table(id int) insert into @Num select number from master..spt_values where number between 1 and 100 and type='p' set nocount offdeclare @str varchar(100) select @str=isnull(@str+','+element,element) from ( select SUBSTRING(val,id,1) element from #tb a join @Num b on SUBSTRING(val,id,1)<>'') as T print(@str) --结果 /* 1,2,3,4 */
我想要段sql,就是select b from A。。后的结果为1,2,3,4 结果集要用逗号隔开。
returns varchar(32)
begin
declare @retValue varchar(32)
set @retValue =''
if (@v is not null)
begin
declare @s varchar(16),
@l tinyint,
@i tinyint
set @i = 1
set @s =convert(varchar(16),@v)
set @l = len(@s)
while(@i<=@l)
begin
set @retValue = @retValue + substring(@s,@i,1)+','
--print @retValue
set @i = @i + 1
end
end
return left(@retValue,len(@retValue)-1)
endselect dbo.fn_getStr(123)--------------------------------
1,2,3,4(1 行受影响)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb ([val] varchar(10))
set nocount on
insert into #tb
select '1234'declare @Num table(id int)
insert into @Num select number from master..spt_values where number between 1 and 100 and type='p'
set nocount offdeclare @str varchar(100)
select @str=isnull(@str+','+element,element) from (
select SUBSTRING(val,id,1) element from #tb a
join @Num b on SUBSTRING(val,id,1)<>'') as T
print(@str)
--结果
/*
1,2,3,4
*/