USE [test]
GO
/****** 对象: UserDefinedFunction [dbo].[CSQL] 脚本日期: 12/03/2008 14:28:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CSQL](@strTable varchar(40),@strField varchar(40), @strKeyWords varchar(4000))
returns varchar(4000)
AS
BEGIN
DECLARE @strSQL varchar(4000)
DECLARE @intMax int
DECLARE @i intset @strKeyWords=Replace(@strKeyWords, ';', ' ')
set @strKeyWords=Replace(@strKeyWords, ':', ' ')
set @strKeyWords=Replace(@strKeyWords, ',', ' ')
set @strKeyWords=Replace(@strKeyWords, ',', ' ')
set @strKeyWords = RTRIM(LTRIM(@strKeyWords))
set @intMax = charindex(' ',@strKeyWords) If @strKeyWords=''
set @strSQL = 'SELECT ' + @strField + ' FROM ' + @strTable
else
set @strSQL= 'SELECT ' + @strField + ' FROM ' + @strTable + ' where '+ @strField + ' like% '+ @strKeyWords +'%'
while @intMax > 0
begin
set @strSQL=@strSQL + @strField + ' like% '+ left(@strKeyWords,@intMax-1) +'% or'
set @strKeyWords=right(@strKeyWords,len(@strKeyWords)-@intMax)
set @intMax=CHARINDEX(',',@strKeyWords)
endreturn @strSQL
end
GO
/****** 对象: UserDefinedFunction [dbo].[CSQL] 脚本日期: 12/03/2008 14:28:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CSQL](@strTable varchar(40),@strField varchar(40), @strKeyWords varchar(4000))
returns varchar(4000)
AS
BEGIN
DECLARE @strSQL varchar(4000)
DECLARE @intMax int
DECLARE @i intset @strKeyWords=Replace(@strKeyWords, ';', ' ')
set @strKeyWords=Replace(@strKeyWords, ':', ' ')
set @strKeyWords=Replace(@strKeyWords, ',', ' ')
set @strKeyWords=Replace(@strKeyWords, ',', ' ')
set @strKeyWords = RTRIM(LTRIM(@strKeyWords))
set @intMax = charindex(' ',@strKeyWords) If @strKeyWords=''
set @strSQL = 'SELECT ' + @strField + ' FROM ' + @strTable
else
set @strSQL= 'SELECT ' + @strField + ' FROM ' + @strTable + ' where '+ @strField + ' like% '+ @strKeyWords +'%'
while @intMax > 0
begin
set @strSQL=@strSQL + @strField + ' like% '+ left(@strKeyWords,@intMax-1) +'% or'
set @strKeyWords=right(@strKeyWords,len(@strKeyWords)-@intMax)
set @intMax=CHARINDEX(',',@strKeyWords)
endreturn @strSQL
end
drop function csql
go
create FUNCTION [dbo].[CSQL](@strTable varchar(40),@strField varchar(40), @strKeyWords varchar(4000))
returns varchar(4000)
AS
BEGIN
DECLARE @strSQL varchar(4000)
DECLARE @intMax int
DECLARE @i int set @strKeyWords=Replace(@strKeyWords, ';', ' ')
set @strKeyWords=Replace(@strKeyWords, ':', ' ')
set @strKeyWords=Replace(@strKeyWords, ',', ' ')
set @strKeyWords=Replace(@strKeyWords, ',', ' ')
set @strKeyWords = RTRIM(LTRIM(@strKeyWords))
set @intMax = charindex(' ',@strKeyWords) If @strKeyWords=''
set @strSQL = 'SELECT ' + @strField + ' FROM ' + @strTable
else
begin
while @intMax>0
begin
set @strSQL=isnull(@strSQL+' or ','SELECT ' + @strField + ' FROM ' + @strTable+' where ') + @strField + ' like ''%'+ left(@strKeyWords,@intMax-1) +'%'' '
set @strKeyWords=right(@strKeyWords,len(@strKeyWords)-@intMax)
set @intMax=CHARINDEX(' ',@strKeyWords)
end
set @strSQL=@strSQL+' or '+ @strField + ' like ''%'+ @strKeyWords +'%'' '
end return @strSQL
end
goselect dbo.csql('tb','id','1,2,3')
/*
SELECT id FROM tb where id like '%1%' or id like '%2%' or id like '%3%'
*/