这个为通用过滤关键字的函数，若有其他关键字未添加可以参考语法加入。
use [db]
go
/****** object: userdefinedfunction [dbo].[f_filterstring] script date: 12/09/2013 17:03:45 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create function [dbo].[f_filterstring] --通用函数。过滤关键字
(@sqlstring nvarchar(1000))--参数为需要过滤的参数
returns nvarchar(1000)
as
begin
declare @fistring nvarchar(20)
declare @count int
set @fistring='select'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='delete'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='drop'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='truncate'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='--'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='update'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='insert into'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='create'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
set @fistring='alter'
set @count= charindex(@fistring,@sqlstring)
if(@count>0)
begin
set @sqlstring=replace(@sqlstring,@fistring,'')
end
return @sqlstring
end
go
这个为通用分割函数：第一个参数为分割前的字符串，第二个参数为分割字符
use [db]
go
/****** object: userdefinedfunction [dbo].[f_splitstr] script date: 09/30/2013 21:26:37 ******/
set ansi_nulls on
go
set quoted_identifier on
go
alter function [dbo].[f_splitstr](@sourcesql varchar(8000),@strseprate varchar(100))
returns @temp table(f1 varchar(100))
as
begin
declare @ch as varchar(100)
set @sourcesql=@sourcesql+@strseprate
while(@sourcesql'')
begin
set @ch=left(@sourcesql,charindex(@strseprate,@sourcesql,1)-1)
insert @temp values(@ch)
set @sourcesql=stuff(@sourcesql,1,charindex(@strseprate,@sourcesql,1),'')
end
return
end
，