Contains like Search in SQL Server 2005

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.

You must Login to comment.