这个为通用过滤关键字的函数,若有其他关键字未添加可以参考语法加入。
USE [Db]
GO/****** Object: UserDefinedFunction [dbo].[f_FilterString] Script Date: 12/09/2013 17:03:45 ******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON
GOCREATE function [dbo].[f_FilterString] --通用函数。过滤关键字
(@SqlString nvarchar(1000))--参数为需要过滤的参数returns nvarchar(1000)as begin declare @FiString nvarchar(20)declare @count intset @FiString='select'set @count= CHARINDEX(@FiString,@SqlString)
if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') endset @FiString='delete'
set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') end set @FiString='drop'set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') endset @FiString='truncate'set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') endset @FiString='--'
set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') end set @FiString='update'set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') endset @FiString='insert into'
set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') endset @FiString='create'set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') endset @FiString='alter'
set @count= CHARINDEX(@FiString,@SqlString)if(@count>0)
beginset @SqlString=replace(@SqlString,@FiString,'') endreturn @SqlStringend
GO
这个为通用分割函数:第一个参数为分割前的字符串,第二个参数为分割字符
USE [Db]
GO/****** Object: UserDefinedFunction [dbo].[f_splitstr] Script Date: 09/30/2013 21:26:37 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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