Contains Like search in SQL Server 2005
This is my first post to wiki. Here I have to show how to contains like search into sql server 2005.
There is no contains like build in function in Sql server 2005.
First of all you create a custom function Like this.
CREATE FUNCTION [dbo].[Contain](@ColumnName varchar(100), @String varchar(8000))
returns @temptable TABLE (items nvarchar(max))
as
begin
declare @idx int
declare @slice varchar(8000)
declare @temp nvarchar(Max)
Set @temp=''
declare @Delimiter char(1)
Set @Delimiter=' '
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@temp)>0)
Set @temp =@temp+ 'OR '+@ColumnName+ ' like ''%'+@slice+'%'''
Else
Set @temp =@temp+ @ColumnName+ ' like ''%'+@slice+'%'''
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
if(len(@temp)>0)
insert into @temptable(Items) values(@temp)
return
end
After create a contain function,you create a stored procedure which tables are queried.Where i used a EmployeeInfo Table.
Create proc SelectEmp
(
@Name nvarchar(200),
@ConditionValue nvarchar(500)
)
--@WhereCon nvarchar(500)
As
Begin
Declare @Sql nvarchar(Max)
Declare @WhereCondition nvarchar(300)
Set @WhereCondition= (select * From [dbo].Contain(@Name,@ConditionValue))
Set @Sql = 'select * from EmployeeInfo'
if(len(@WhereCondition)>0 And @WhereCondition is not null)
Set @Sql= @Sql+' Where ' +Replace(@WhereCondition,'''','''')
EXEC sp_executesql @SQL
End
If all doing this, you can call your SP from server side or where to you used.
Examples:
SelectEmp 'Name','Mountain Dew'
SelectEmp 'Name','Dew Mountain'
SelectEmp 'Name','Mountain'
SelectEmp 'Name','Dew'
Thank you.
