declare @str varchar(128); set @str = '[xxx][aaa][bbb][ccc][ddd][xxx]'; set @str = LEFT(@str,LEN(@str)-1); set @str = right(@str,LEN(@str)-1)
declare @table table ( n varchar(128) ) declare @char varchar(128); set @char = (select SUBSTRING(@str,0,CHARINDEX('][',@str))); while(len(@char) > 0) begin insert into @table values(@char); set @str = (select SUBSTRING(@str,LEN(@char)+3,len(@str))); set @char = (select SUBSTRING(@str,0,CHARINDEX('][',@str))); end insert into @table values(@str); select * from @table;
---1.创建函数 create function [dbo].[split] ( @SourceSql varchar(8000), @StrSeprate varchar(10) ) returns @temp table(F1 varchar(100)) as begin declare @i int set @SourceSql = rtrim(ltrim(@SourceSql)) set @i = charindex(@StrSeprate,@SourceSql) while @i >= 1 begin if len(left(@SourceSql,@i-1))>0 begin insert @temp values(left(@SourceSql,@i-1)) end set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql <> '' insert @temp values(@SourceSql) return end --2.运行 declare @str varchar(8000) Select @str= Replace(Replace(Replace('[xxx][aaa][bbb][ccc][ddd][xxx]','][',','),'[',''),']','') select * from split(@str,',')
declare @s varchar(1000) set @s='[xxx][aaa][bbb][ccc][ddd][xxx]' set @s='select '''+replace(replace(@s,']',''' as name union all select '''),'[','')set @s=substring(@s,1,len(@s)-18)
exec(@s) name ---- xxx aaa bbb ccc ddd xxx(6 行受影响)
declare @str varchar(100) set @str='[xxx][aaa][bbb][ccc][ddd][xxx]'select @str=replace(replace(replace(@str,'][',','),'[',''),']',',')select substring(@str,b1.id,charindex(',',@str,b1.id)-b1.id) from b1 where substring(','+@str,b1.id,1)=',' xxx aaa bbb ccc ddd
上面弄错了。 declare @str varchar(100) set @str='[xxx][aaa][bbb][ccc][ddd][xxx]' select @str=replace(replace(replace(@str,'][',','),'[',''),']','') select top 100 id=identity(int,1,1)into # from syscolumns select substring(@str,b1.id,charindex(',',@str+',',b1.id)-b1.id) from # b1 where substring(','+@str,b1.id,1)=','xxx aaa bbb ccc ddd xxx
Select 'xxx' union all Select 'aaa' union all Select 'bbb' union all select 'ccc' union all select 'ddd' union all select 'xxx'
sql server并无split函数,只用用charindex函数定位,再一个个取出 也可用以入小技巧,来转换 declare @str nvarchar(4000),@sqlstr nvarchar(4000) set @str='[xxx][aaa][bbb][ccc][ddd][xxx]' create table #tmp (fstr nvarchar(100)) --假设长度<100字符 set @sqlstr=replace(@str,'][',''') insert into #tmp values(''') set @sqlstr=replace(@sqlstr,'[','insert into #tmp values(''') set @sqlstr=replace(@sqlstr,']',''')') exec(@sqlstr) select * from #tmpdrop table #tmpfstr ---------------------------------------------------------------------------------------------------- xxx aaa bbb ccc ddd xxx(6 行受影响)
with cte as( select 0 as id,'[xxx][aaa][bbb][ccc][ddd][xxx]' as c2 union all select id+1 as id,c2 from cte where id<5 )select substring(c2,id*5+2,3)as c from cte /* c ------ xxx aaa bbb ccc ddd xxx(6 行受影响)*/
select substring(c2,number*5+2,3)as c from( select number,'[xxx][aaa][bbb][ccc][ddd][xxx]' as c2 from master..spt_values where type='p' and number<6 )t /*c ------ xxx aaa bbb ccc ddd xxx(6 行受影响) */
declare @str varchar(128);
set @str = '[xxx][aaa][bbb][ccc][ddd][xxx]';
set @str = LEFT(@str,LEN(@str)-1);
set @str = right(@str,LEN(@str)-1)
declare @table table
(
n varchar(128)
) declare @char varchar(128); set @char = (select SUBSTRING(@str,0,CHARINDEX('][',@str))); while(len(@char) > 0) begin insert into @table values(@char);
set @str = (select SUBSTRING(@str,LEN(@char)+3,len(@str)));
set @char = (select SUBSTRING(@str,0,CHARINDEX('][',@str))); end insert into @table values(@str); select * from @table;
---1.创建函数
create function [dbo].[split]
( @SourceSql varchar(8000), @StrSeprate varchar(10) )
returns @temp table(F1 varchar(100))
as begin
declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql))
set @i = charindex(@StrSeprate,@SourceSql)
while @i >= 1
begin
if len(left(@SourceSql,@i-1))>0
begin
insert @temp values(left(@SourceSql,@i-1))
end
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql <> ''
insert @temp values(@SourceSql)
return
end --2.运行
declare @str varchar(8000)
Select @str= Replace(Replace(Replace('[xxx][aaa][bbb][ccc][ddd][xxx]','][',','),'[',''),']','')
select * from split(@str,',')
set @s='[xxx][aaa][bbb][ccc][ddd][xxx]'
set @s='select '''+replace(replace(@s,']',''' as name union all select '''),'[','')set @s=substring(@s,1,len(@s)-18)
exec(@s)
name
----
xxx
aaa
bbb
ccc
ddd
xxx(6 行受影响)
set @str='[xxx][aaa][bbb][ccc][ddd][xxx]'select @str=replace(replace(replace(@str,'][',','),'[',''),']',',')select substring(@str,b1.id,charindex(',',@str,b1.id)-b1.id) from b1 where substring(','+@str,b1.id,1)=','
xxx
aaa
bbb
ccc
ddd
declare @str varchar(100)
set @str='[xxx][aaa][bbb][ccc][ddd][xxx]'
select @str=replace(replace(replace(@str,'][',','),'[',''),']','')
select top 100 id=identity(int,1,1)into # from syscolumns
select substring(@str,b1.id,charindex(',',@str+',',b1.id)-b1.id) from # b1 where substring(','+@str,b1.id,1)=','xxx
aaa
bbb
ccc
ddd
xxx
Select 'aaa' union all
Select 'bbb' union all
select 'ccc' union all
select 'ddd' union all
select 'xxx'
也可用以入小技巧,来转换
declare @str nvarchar(4000),@sqlstr nvarchar(4000)
set @str='[xxx][aaa][bbb][ccc][ddd][xxx]'
create table #tmp (fstr nvarchar(100)) --假设长度<100字符
set @sqlstr=replace(@str,'][',''') insert into #tmp values(''')
set @sqlstr=replace(@sqlstr,'[','insert into #tmp values(''')
set @sqlstr=replace(@sqlstr,']',''')')
exec(@sqlstr)
select * from #tmpdrop table #tmpfstr
----------------------------------------------------------------------------------------------------
xxx
aaa
bbb
ccc
ddd
xxx(6 行受影响)
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxxaaabbbcccdddxxx(1 行受影响)*/
select 0 as id,'[xxx][aaa][bbb][ccc][ddd][xxx]' as c2
union all
select id+1 as id,c2 from cte where id<5
)select substring(c2,id*5+2,3)as c from cte
/*
c
------
xxx
aaa
bbb
ccc
ddd
xxx(6 行受影响)*/
select number,'[xxx][aaa][bbb][ccc][ddd][xxx]' as c2 from master..spt_values where type='p' and number<6
)t
/*c
------
xxx
aaa
bbb
ccc
ddd
xxx(6 行受影响)
*/