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.
The ASP.NET Wiki was started by Scott Hanselman in February of 2008. The idea is that folks spend a lot of time trolling the blogs, googlinglive-searching for answers to common "How To" questions. There's piles of fantastic community-created and MSFT-created content out there, but if it's not found by a search engine and the right combination of keywords, it's often lost.
The ASP.NET Wiki articles moved to CodeProject in October 2013 and will live on, loved, protected and updated by the community.