create procedure replace_for as update tb set col1=replace(col1,'-',',')
create function get_r (@s varchar(8000)) returns int as begin declare @s1 varchar(8000) declare @i int select @s1=@s while charindex(',',@s1)>0 begin select @s1=stuff(@s1,1,charindex(',',@s),'') end select @i=cast(@s1 as int) return @i end godeclare @s varchar(1000) set @s='1,2,3-4,6,7,8-14' declare @result varchar(1000) set @result='' set @s=@s+',' while charindex('-',@s)>0 begin select @result=@result+','+left(@s,charindex('-',@s)-1) select @s=stuff(@s,1,charindex('-',@s),'') if (dbo.get_r(@result)+1)<>(cast(substring(@s,1,charindex(',',@s)-1) as int)) select @s=cast((dbo.get_r(@result)+1) as varchar)+'-'+@s end select @result=stuff(@result,1,1,'') print @resultdrop function get_r------------------------- 1,2,3,4,6,7,8,9,10,11,12,13
改一下: create function get_r (@s varchar(8000)) returns int as begin declare @s1 varchar(8000) declare @i int select @s1=@s while charindex(',',@s1)>0 begin select @s1=stuff(@s1,1,charindex(',',@s),'') end select @i=cast(@s1 as int) return @i end godeclare @s varchar(1000) set @s='1,2,3-4,6,7,8-14' declare @result varchar(1000) set @result='' set @s=@s+',' while charindex('-',@s)>0 begin select @result=@result+','+left(@s,charindex('-',@s)-1) select @s=stuff(@s,1,charindex('-',@s),'') if (dbo.get_r(@result)+1)<>(cast(substring(@s,1,charindex(',',@s)-1) as int)) select @s=cast((dbo.get_r(@result)+1) as varchar)+'-'+@s end select @result=stuff(@result,1,1,'')+','+@s select @result=substring(@result,1,len(@result)-1) print @resultdrop function get_r---------------------------- 1,2,3,4,6,7,8,9,10,11,12,13,14
--------------創建用戶定義函數------------------ CREATE function dbo.f_str(@str varchar(1000)) returns varchar(50) as begin declare @ret varchar(50) --返回的字符串 declare @start int,@last int set @ret=@str if charindex('-',@str)>0 begin set @start=convert(int,left(@str,charindex('-',@str)-1)) set @last=convert(int,right(@str,len(@str)-charindex('-',@str))) set @ret=convert(varchar,@start) while @start<@last begin set @start=@start+1 set @ret=@ret+','+convert(varchar,@start) end end return @ret end GOdeclare @str nvarchar(100),@sql nvarchar(4000) set @str='1,2,3-4,6,7,8-10' set @sql=' select '+replace('dbo.f_str('''+@str+''')',',',''') union all select dbo.f_str(''') exec (@sql)drop function f_str /* 1 2 3,4 6 7 8,9,10 */
有点绕远,仅供参考。 --------------創建用戶定義函數------------------ CREATE function dbo.f_str(@str varchar(1000)) returns varchar(50) as begin declare @ret varchar(50) --返回的字符串 declare @start int,@last int set @ret=@str if charindex('-',@str)>0 begin set @start=convert(int,left(@str,charindex('-',@str)-1)) set @last=convert(int,right(@str,len(@str)-charindex('-',@str))) set @ret=convert(varchar,@start) while @start<@last begin set @start=@start+1 set @ret=@ret+','+convert(varchar,@start) end end return @ret end GOdeclare @str nvarchar(100),@sql nvarchar(4000),@sql1 nvarchar(4000) set @str='1,2,3-4,6,7,8-10' set @sql1='' set @sql='select @sql1=@sql1+'',''+col from ('+ 'select '+replace('dbo.f_str('''+@str+''')',',',''' )[col] union all select dbo.f_str(''')+') a' exec sp_executesql @sql,N'@sql1 nvarchar(4000) output',@sql1 output select stuff(@sql1,1,1,'') drop function f_str /* 1,2,3,4,6,7,8,9,10 */
declare @str nvarchar(100),@count int,@num1 int,@num2 int select @str='1,2,3,4-6,7,8-15,19-30' select @str=','+@str+',' while len(@str)-len(replace(@str,'-',''))>0 begin select @count=charindex('-',@str) select @num1=reverse(substring(reverse(substring(@str,1,@count-1)),1,charindex(',',reverse(substring(@str,1,@count-1)))-1)) select @num2=substring(@str,@count+1,charindex(',',(substring(@str,@count+1,len(@str))))-1) if @num2-@num1=1 set @str=stuff(@str,@count,1,',') else set @str=stuff(@str,charindex('-',@str),1,','+cast((@num1+1)as nvarchar(100))+'-') end print reverse(stuff(reverse(stuff(@str,1,1,'')),1,1,'')) --输入 '1,2,3,4-6,7,8-15,19-30' ----结果 '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,19,20,21,22,23,24,25,26,27,28,29,30'
IF '-'的后值减去'-'的前值大于1 THEN REPLACE '-' TO ','&('-'的前值加1)&'-'
declare @s varchar(1000) declare @s1 varchar(1000) declare @s2 varchar(4000) declare @inti int declare @intj int declare @intk int declare @currently varchar(500) declare @intposition int set @s='2,3-4,6,7,8-14,' set @s1=@s set @s2='' set @inti=0 set @intj=0 while charindex(',',@s1)>0 begin set @currently=left(@s1,charindex(',',@s1)-1) set @s1=right(@s1,len(@s1)-charindex(',',@s1)) if charindex('-',@currently)>0 begin set @inti=left(@currently,charindex('-',@currently)-1) set @intj=right(@currently,len(@currently)-charindex('-',@currently)) while @inti<@intj begin set @s2=@s2+ ltrim( convert(varchar(20),@inti)) +',' set @inti=@inti+1 end set @s2=@s2+ ltrim( convert(varchar(20),@inti)) +',' end else set @s2=@s2 + @currently +',' end select @s2
as
update tb set col1=replace(col1,'-',',')
returns int
as
begin
declare @s1 varchar(8000)
declare @i int
select @s1=@s
while charindex(',',@s1)>0
begin
select @s1=stuff(@s1,1,charindex(',',@s),'')
end
select @i=cast(@s1 as int)
return @i
end
godeclare @s varchar(1000)
set @s='1,2,3-4,6,7,8-14'
declare @result varchar(1000)
set @result=''
set @s=@s+','
while charindex('-',@s)>0
begin
select @result=@result+','+left(@s,charindex('-',@s)-1)
select @s=stuff(@s,1,charindex('-',@s),'')
if (dbo.get_r(@result)+1)<>(cast(substring(@s,1,charindex(',',@s)-1) as int))
select @s=cast((dbo.get_r(@result)+1) as varchar)+'-'+@s
end
select @result=stuff(@result,1,1,'')
print @resultdrop function get_r-------------------------
1,2,3,4,6,7,8,9,10,11,12,13
create function get_r (@s varchar(8000))
returns int
as
begin
declare @s1 varchar(8000)
declare @i int
select @s1=@s
while charindex(',',@s1)>0
begin
select @s1=stuff(@s1,1,charindex(',',@s),'')
end
select @i=cast(@s1 as int)
return @i
end
godeclare @s varchar(1000)
set @s='1,2,3-4,6,7,8-14'
declare @result varchar(1000)
set @result=''
set @s=@s+','
while charindex('-',@s)>0
begin
select @result=@result+','+left(@s,charindex('-',@s)-1)
select @s=stuff(@s,1,charindex('-',@s),'')
if (dbo.get_r(@result)+1)<>(cast(substring(@s,1,charindex(',',@s)-1) as int))
select @s=cast((dbo.get_r(@result)+1) as varchar)+'-'+@s
end
select @result=stuff(@result,1,1,'')+','+@s
select @result=substring(@result,1,len(@result)-1)
print @resultdrop function get_r----------------------------
1,2,3,4,6,7,8,9,10,11,12,13,14
CREATE function dbo.f_str(@str varchar(1000))
returns varchar(50)
as
begin
declare @ret varchar(50) --返回的字符串
declare @start int,@last int
set @ret=@str
if charindex('-',@str)>0
begin
set @start=convert(int,left(@str,charindex('-',@str)-1))
set @last=convert(int,right(@str,len(@str)-charindex('-',@str)))
set @ret=convert(varchar,@start)
while @start<@last
begin
set @start=@start+1
set @ret=@ret+','+convert(varchar,@start)
end
end
return @ret
end
GOdeclare @str nvarchar(100),@sql nvarchar(4000)
set @str='1,2,3-4,6,7,8-10'
set @sql=' select '+replace('dbo.f_str('''+@str+''')',',',''') union all select dbo.f_str(''')
exec (@sql)drop function f_str
/*
1
2
3,4
6
7
8,9,10
*/
--------------創建用戶定義函數------------------
CREATE function dbo.f_str(@str varchar(1000))
returns varchar(50)
as
begin
declare @ret varchar(50) --返回的字符串
declare @start int,@last int
set @ret=@str
if charindex('-',@str)>0
begin
set @start=convert(int,left(@str,charindex('-',@str)-1))
set @last=convert(int,right(@str,len(@str)-charindex('-',@str)))
set @ret=convert(varchar,@start)
while @start<@last
begin
set @start=@start+1
set @ret=@ret+','+convert(varchar,@start)
end
end
return @ret
end
GOdeclare @str nvarchar(100),@sql nvarchar(4000),@sql1 nvarchar(4000)
set @str='1,2,3-4,6,7,8-10'
set @sql1=''
set @sql='select @sql1=@sql1+'',''+col from ('+ 'select '+replace('dbo.f_str('''+@str+''')',',',''' )[col] union all select dbo.f_str(''')+') a'
exec sp_executesql @sql,N'@sql1 nvarchar(4000) output',@sql1 output
select stuff(@sql1,1,1,'')
drop function f_str
/*
1,2,3,4,6,7,8,9,10
*/
select @str='1,2,3,4-6,7,8-15,19-30'
select @str=','+@str+','
while len(@str)-len(replace(@str,'-',''))>0
begin
select @count=charindex('-',@str)
select @num1=reverse(substring(reverse(substring(@str,1,@count-1)),1,charindex(',',reverse(substring(@str,1,@count-1)))-1))
select @num2=substring(@str,@count+1,charindex(',',(substring(@str,@count+1,len(@str))))-1)
if @num2-@num1=1
set @str=stuff(@str,@count,1,',')
else
set @str=stuff(@str,charindex('-',@str),1,','+cast((@num1+1)as nvarchar(100))+'-')
end
print reverse(stuff(reverse(stuff(@str,1,1,'')),1,1,''))
--输入 '1,2,3,4-6,7,8-15,19-30'
----结果 '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,19,20,21,22,23,24,25,26,27,28,29,30'
declare @s varchar(1000)
declare @s1 varchar(1000)
declare @s2 varchar(4000)
declare @inti int
declare @intj int
declare @intk int
declare @currently varchar(500)
declare @intposition int
set @s='2,3-4,6,7,8-14,'
set @s1=@s
set @s2=''
set @inti=0
set @intj=0
while charindex(',',@s1)>0
begin
set @currently=left(@s1,charindex(',',@s1)-1)
set @s1=right(@s1,len(@s1)-charindex(',',@s1))
if charindex('-',@currently)>0
begin
set @inti=left(@currently,charindex('-',@currently)-1)
set @intj=right(@currently,len(@currently)-charindex('-',@currently))
while @inti<@intj
begin
set @s2=@s2+ ltrim( convert(varchar(20),@inti)) +','
set @inti=@inti+1
end
set @s2=@s2+ ltrim( convert(varchar(20),@inti)) +','
end
else
set @s2=@s2 + @currently +','
end
select @s2