这个为通用过滤关键字的函数，若有其他关键字未添加可以参考语法加入。

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