declare @s varchar(100) set @s='qq,ww,ee,rr'select replace(replace(replace(replace(@s,'qq','qq as qq1'),'ww','ww as ww1'),'ee','ee as ee1'),'rr','rr as rr1')
declare @str nvarchar(200) set @str='qq,ww,ee,rr' ;with cte as( select convert(varchar(10),left(@str,charindex(',',@str)-1))as lt,convert(varchar(100),right(@str,len(@str)-charindex(',',@str))+',') as rt union all select convert(varchar(10),left(rt,charindex(',',rt)-1))as lt,convert(varchar(100),right(rt,len(rt)-charindex(',',rt))) as rt from cte where len(rt)>1 ) select left(lt,len(lt)-1)lt from ( select stuff((select lt+' as '+lt+'1,' from cte for xml path('')),1,0,'')as lt )t /*lt ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- qq as qq1,ww as ww1,ee as ee1,rr as rr1(1 行受影响)*/
create function f_getstr(@a varchar(1000)) returns varchar(1000) as begin declare @str varchar(1000) declare @rrs varchar(1000) declare @res varchar(1000) set @res = '' set @str = @a + ',' while(charindex(',',@str) > 0) begin set @rrs = substring(@str,1,charindex(',',@str) - 1) set @res = @res + ',' + @rrs + ' as ' + @rrs + '1' set @str = substring(@str,charindex(',',@str) + 1,len(@str) - charindex(',',@str) + 1) end return stuff(@res,1,1,'') end godeclare @str varchar(1000) set @str = 'qq,ww,ee,rr'select dbo.f_getstr(@str)drop function f_getstr ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- qq as qq1,ww as ww1,ee as ee1,rr as rr1(1 行受影响)
我的描述有点问题 我这个字符串是动态生成的 不是死的 可能是 如“qq,ww,ee,rr” 替换成 “qq as qq1,ww as ww1,ee as ee1,rr as rr1”也可能是 如“aa,ss,dd,ff” 替换成 “aa as aa1,ss as ss1,dd as dd1,ff as ff1”
go create function [dbo].[m_split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(200)) 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),'') -- SET @c = substring(@c,charindex(' ',@c)+1,len(@c)) end insert @t(col) values (@c) return end go declare @sql varchar(20) set @sql='qq,ww,ee,rr'declare @a varchar(100) set @a='' select @a=@a+col+' as '+col+'1,' from m_split(@sql,',') set @a=substring(@a,1,len(@a)-1) select @a /* qq as qq1,ww as ww1,ee as ee1,rr as rr1 */
--SQL 2008declare @str nvarchar(2000)='qq,ww,ee,rr' set @str+=',' declare @len int=len(@str),@index int=1,@tempIndex int=0; while(@index<=@len) begin if(SUBSTRING(@str,@index,1)=',') begin declare @char nvarchar(100)=SUBSTRING(@str,@tempIndex,@index-@tempIndex); declare @replaceChar nvarchar(100)=@char+' as '+@char+'1'; set @str=REPLACE(@str,@char,@replaceChar); select @len=LEN(@str),@index+=(len(@replaceChar)-len(@char)); set @index+=1; set @tempIndex=@index; --select @str,@index end else begin set @index+=1; end end select left(@str,LEN(@str)-1) as Result Result ---------------------------------------------- qq as qq1,ww as ww1,ee as ee1,rr as rr1
create table tbchar(chars varchar(100)) insert into tbchar select 'qq,ww,ee,rr' union all select 'qdf,ww,ee,rr' union all select 'q34,21ww,e5e,r6r' union all select 'qq,w5w,ee,r323r' select stuff((SELECT ','+id+' as '+id+'1' FROM (SELECT [value] = CONVERT(XML , '<r>' + REPLACE(t.chars , ',' , '</r><r>')+ '</r>') ) A OUTER APPLY ( SELECT id = N.r.value('.' , 'varchar(100)') FROM A.[value].nodes('/r') N (r) ) B for xml path('')),1,1,'') from tbchar t---------------------------------------------------------- qq as qq1,ww as ww1,ee as ee1,rr as rr1 qdf as qdf1,ww as ww1,ee as ee1,rr as rr1 q34 as q341,21ww as 21ww1,e5e as e5e1,r6r as r6r1 qq as qq1,w5w as w5w1,ee as ee1,r323r as r323r1
set @s='qq,ww,ee,rr'select replace(replace(replace(replace(@s,'qq','qq as qq1'),'ww','ww as ww1'),'ee','ee as ee1'),'rr','rr as rr1')
set @str='qq,ww,ee,rr'
;with cte as(
select convert(varchar(10),left(@str,charindex(',',@str)-1))as lt,convert(varchar(100),right(@str,len(@str)-charindex(',',@str))+',') as rt
union all
select convert(varchar(10),left(rt,charindex(',',rt)-1))as lt,convert(varchar(100),right(rt,len(rt)-charindex(',',rt))) as rt from cte where len(rt)>1
)
select left(lt,len(lt)-1)lt from (
select stuff((select lt+' as '+lt+'1,' from cte for xml path('')),1,0,'')as lt
)t
/*lt
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qq as qq1,ww as ww1,ee as ee1,rr as rr1(1 行受影响)*/
create function f_getstr(@a varchar(1000))
returns varchar(1000)
as
begin
declare @str varchar(1000)
declare @rrs varchar(1000)
declare @res varchar(1000)
set @res = ''
set @str = @a + ','
while(charindex(',',@str) > 0)
begin
set @rrs = substring(@str,1,charindex(',',@str) - 1)
set @res = @res + ',' + @rrs + ' as ' + @rrs + '1'
set @str = substring(@str,charindex(',',@str) + 1,len(@str) - charindex(',',@str) + 1)
end
return stuff(@res,1,1,'')
end
godeclare @str varchar(1000)
set @str = 'qq,ww,ee,rr'select dbo.f_getstr(@str)drop function f_getstr
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qq as qq1,ww as ww1,ee as ee1,rr as rr1(1 行受影响)
如“qq,ww,ee,rr”
替换成 “qq as qq1,ww as ww1,ee as ee1,rr as rr1”也可能是
如“aa,ss,dd,ff”
替换成 “aa as aa1,ss as ss1,dd as dd1,ff as ff1”
go
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
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),'')
-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))
end
insert @t(col) values (@c)
return
end
go
declare @sql varchar(20)
set @sql='qq,ww,ee,rr'declare @a varchar(100)
set @a=''
select @a=@a+col+' as '+col+'1,' from m_split(@sql,',')
set @a=substring(@a,1,len(@a)-1)
select @a
/*
qq as qq1,ww as ww1,ee as ee1,rr as rr1
*/
--SQL 2008declare @str nvarchar(2000)='qq,ww,ee,rr'
set @str+=','
declare @len int=len(@str),@index int=1,@tempIndex int=0;
while(@index<=@len)
begin
if(SUBSTRING(@str,@index,1)=',')
begin
declare @char nvarchar(100)=SUBSTRING(@str,@tempIndex,@index-@tempIndex);
declare @replaceChar nvarchar(100)=@char+' as '+@char+'1';
set @str=REPLACE(@str,@char,@replaceChar);
select @len=LEN(@str),@index+=(len(@replaceChar)-len(@char));
set @index+=1;
set @tempIndex=@index;
--select @str,@index
end
else
begin
set @index+=1;
end
end
select left(@str,LEN(@str)-1) as Result
Result
----------------------------------------------
qq as qq1,ww as ww1,ee as ee1,rr as rr1
insert into tbchar select 'qq,ww,ee,rr'
union all select 'qdf,ww,ee,rr'
union all select 'q34,21ww,e5e,r6r'
union all select 'qq,w5w,ee,r323r'
select
stuff((SELECT ','+id+' as '+id+'1'
FROM (SELECT [value] = CONVERT(XML , '<r>' + REPLACE(t.chars , ',' , '</r><r>')+ '</r>')
) A
OUTER APPLY ( SELECT id = N.r.value('.' , 'varchar(100)')
FROM A.[value].nodes('/r') N (r)
) B
for xml path('')),1,1,'')
from tbchar t----------------------------------------------------------
qq as qq1,ww as ww1,ee as ee1,rr as rr1
qdf as qdf1,ww as ww1,ee as ee1,rr as rr1
q34 as q341,21ww as 21ww1,e5e as e5e1,r6r as r6r1
qq as qq1,w5w as w5w1,ee as ee1,r323r as r323r1