declare @re varchar(4000) declare @str varchar(8000) set @str=',1-1-1,1-1-39,1-1-77,1-1-229,1-1-115,1-1-153,1-1-191,1-1-305,1-1-343,1-1-381,1-1-267,....' set @re=@str select replace(@re,'1-1-','') select @re
declare @str varchar(8000) set @str='1-1-1,1-1-39,1-1-77,1-1-229,1-1-115,1-1-153,1-1-191,1-1-305,1-1-343,1-1-381,1-1-267'set @str = replace(@str,',',' union all select ') --print @str create table ##tmp(a varchar(20)) Exec ('insert ##tmp Select '+@str) --select * from ##tmpset @str='' select @str=@str+','+right(a,len(a)-1) from ##tmp set @str=right(@str,len(@str)-1) Select distinct @str from ##tmp 1,39,77,229,115,153,191,305,343,381,267(所影响的行数为 1 行) -- drop table ##tmp
--自定义函数 create function f_getstr(@str varchar(8000)) returns varchar(8000) as begin declare @tb table(id int identity(1,1),aa int) declare @re varchar(8000)insert into @tb(aa) select top 8000 a.id from (select top 100 id from syscolumns) a ,(select top 100 id from syscolumns) b ,(select top 100 id from syscolumns) cset @re='' select @re=@re+','+left(a,charindex('-',a)-1) from( select a=reverse(substring(a,b.id,charindex(',',a+',',b.id)-b.id)) from (select a=@str) a,@tb b where substring(','+a,b.id,1)=',' ) a where a like '%-%-%' set @re=substring(@re,2,8000) return(@re) end go --测试 declare @str varchar(8000) set @str=',1-1-1,1-1-39,1-1-77,1-1-229,1-1-115,1-1-153,1-1-191,1-1-305,1-1-343,1-1-381,1-1-267,'select dbo.f_getstr(@str)
declare @str varchar(8000)
set @str=',1-1-1,1-1-39,1-1-77,1-1-229,1-1-115,1-1-153,1-1-191,1-1-305,1-1-343,1-1-381,1-1-267,....'
set @re=@str
select replace(@re,'1-1-','')
select @re
set @str='1-1-1,1-1-39,1-1-77,1-1-229,1-1-115,1-1-153,1-1-191,1-1-305,1-1-343,1-1-381,1-1-267'set @str = replace(@str,',',' union all select ')
--print @str
create table ##tmp(a varchar(20))
Exec ('insert ##tmp Select '+@str)
--select * from ##tmpset @str=''
select @str=@str+','+right(a,len(a)-1) from ##tmp
set @str=right(@str,len(@str)-1)
Select distinct @str from ##tmp
1,39,77,229,115,153,191,305,343,381,267(所影响的行数为 1 行)
-- drop table ##tmp
create function f_getstr(@str varchar(8000))
returns varchar(8000)
as
begin
declare @tb table(id int identity(1,1),aa int)
declare @re varchar(8000)insert into @tb(aa)
select top 8000 a.id from
(select top 100 id from syscolumns) a
,(select top 100 id from syscolumns) b
,(select top 100 id from syscolumns) cset @re=''
select @re=@re+','+left(a,charindex('-',a)-1)
from(
select a=reverse(substring(a,b.id,charindex(',',a+',',b.id)-b.id))
from (select a=@str) a,@tb b
where substring(','+a,b.id,1)=','
) a where a like '%-%-%'
set @re=substring(@re,2,8000)
return(@re)
end
go
--测试
declare @str varchar(8000)
set @str=',1-1-1,1-1-39,1-1-77,1-1-229,1-1-115,1-1-153,1-1-191,1-1-305,1-1-343,1-1-381,1-1-267,'select dbo.f_getstr(@str)
---------------
但是不一定是1-1-?的
还a-b-c都是从1开始的整数