大家帮写一个自定义函数吧。谢谢了。
功能是:
对于一个字符串,有可能是下列形式:
下面字符串结果要返回3,即一共3页
1,4,67 (表示第一页,第四页,第67页)下面字符串要返回7,即一共7页
1,5-9,3 (表示第一页,第五页到第九页,第三页)下面字符串要返回4,即一共4页
3,5-7 (表示第三页,第五页到第七页)要求函数的参数就是字符串,返回值就是数字,如何写呀,谢谢大家
功能是:
对于一个字符串,有可能是下列形式:
下面字符串结果要返回3,即一共3页
1,4,67 (表示第一页,第四页,第67页)下面字符串要返回7,即一共7页
1,5-9,3 (表示第一页,第五页到第九页,第三页)下面字符串要返回4,即一共4页
3,5-7 (表示第三页,第五页到第七页)要求函数的参数就是字符串,返回值就是数字,如何写呀,谢谢大家
--建立函数
create function fn_Num(
@str varchar(500)
)
returns int
as
begin
declare @r int
declare @x varchar(50)
declare @i int
declare @j int set @r=0
while charindex(',',@str)>0
begin
set @x=left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
if charindex('-',@x)>0
begin
set @i=left(@x,charindex('-',@x)-1)
set @j=stuff(@x,1,charindex('-',@x),'')
set @r=@r+@j-@i+1
end
else
set @r=@r++1
end
if @str<>''
if charindex('-',@str)>0
begin
set @i=left(@str,charindex('-',@str)-1)
set @j=stuff(@str,1,charindex('-',@str),'')
set @r=@r+@j-@i+1
end
else
set @r=@r++1
return @r
endgo--测试
select dbo.fn_Num('1,4,67') as [1,4,67],
dbo.fn_Num('1,5-9,3') as [1,5-9,3],
dbo.fn_Num('3,5-7') as [3,5-7],
dbo.fn_Num('1-100,190') as [1-100,190]--结果
1,4,67 1,5-9,3 3,5-7 1-100,190
----------- ----------- ----------- -----------
3 7 4 101(所影响的行数为 1 行)
returns int
as
begin
declare @i int
declare @t table(a varchar(10),b int)
set @s=@s+','
while charindex(',',@s)>0
begin
insert @t(a) select left(@s,charindex(',',@s)-1)
set @s=right(@s,len(@s)-charindex(',',@s))
end
select @i =sum(b) from (select a,b=case when charindex('-',a)>0 then -cast(left(a,charindex('-',a)-1) as int) + cast(right(a,len(a)-charindex('-',a)) as int)+1 else 1 end from @t )tt
return @i
endgo select dbo.fun_fun('1,5-9,3')
--建立函数
create function fn_Num(@str varchar(500))
returns int
as
begin
declare @r int
declare @x varchar(50)
declare @i int
declare @j int set @r=0
set @str=@str+',' --给字符串加上一个逗号可以不用另外判断
while charindex(',',@str)>0
begin
set @x=left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
if charindex('-',@x)>0
begin
set @i=left(@x,charindex('-',@x)-1)
set @j=stuff(@x,1,charindex('-',@x),'')
set @r=@r+@j-@i+1
end
else
set @r=@r++1
end
return @r
endgo--测试
select dbo.fn_Num('1,4,67') as [1,4,67],
dbo.fn_Num('1,5-9,3') as [1,5-9,3],
dbo.fn_Num('3,5-7') as [3,5-7],
dbo.fn_Num('1-100,190') as [1-100,190]--删除函数
drop function fn_Num
create function aaa(@a varchar(50)) returns int as
begin
declare @i int,@c varchar(20)
select @i=0,@a=@a+',',@c=''
while charindex(',',@a)>0
begin
set @c=left(@a,charindex(',',@a)-1)
if charindex('-',@c)>0
set @i=@i-left(@c,charindex('-',@c)-1)+stuff(@c,1,charindex('-',@c),'')
select @i=@i+1,@a=stuff(@a,1,charindex(',',@a),'')
end
return @i
end
----------------------------
select dbo.aaa('2,3-9,7,2-5')
-----------------------------
13
declare @j as int
declare @k as int
declare @r as int
declare @str as varchar(40)select @str = '1-2,4,5-9,3,5-8'select @str = ','+@str+','
while(1=1)
begin
select @i = charindex(',',@str)
select @j = charindex(',',@str,@i+1)
if @i*@j>0
begin
select @k = charindex('-',@str,@i+1)
if not (@k > @i and @k < @j )
select @str = stuff(@str,@i,@j-@i,'+1')
else if ((@k > @i and @k < @j ))
select @str = stuff(@str,@i,1,'+')
end
else
break
endselect @str = 'select ' + reverse(left(@str,len(@str)-1))+'0'exec(@str)
-------------------结果
10