MSSQL下的自定义函数示例

自定义函数示例

 

IF OBJECT_ID(N'dbo.charLastIndexOf', N'FN') IS NOT NULL
    DROP FUNCTION dbo.charLastIndexOf;
GO

create function dbo.charLastIndexOf
(
 @checkChar nvarchar(100),
 @fromChar nvarchar(1000)
)
returns int
as
begin
 declare @indexOf int
 declare @from nvarchar(1000)
 declare @sum int
 set @sum=0
 set @indexOf=0
 set @from = @fromChar
 while(1=1)
 begin  
  set @indexOf = charindex(@checkChar,@from)
  if(@indexOf=0) break
  set @sum=@indexOf+@sum
  set @from = substring(@from,@indexOf+1,LEN(@from))
 end
return @sum
end
go

declare @temp varchar(30)
set @temp='abhc-yh-003-'
select dbo.charLastIndexOf('-',@temp) as myvalue,len(@temp) as length

select * from fs_news
select top 1 content,dbo.charLastIndexOf('p',Content) as myvalue,charindex('p',content) from fs_news