ComfortablyNumb
asked on
TSQL Search on String
Hi Experts,
I would like to create a stored proc that would search on the exact term keyed in (could be one word or phrase and then search on the seperate words in the phase:
Sample Search "Experts Exchange"
WHERE = like 'experts exchange' OR like 'experts' OR like 'exchange'
What is the best practice for this (speed etc)? Im going to take out all stop words so I just get a search on only the keywords typed.
Regs,
Numb
I would like to create a stored proc that would search on the exact term keyed in (could be one word or phrase and then search on the seperate words in the phase:
Sample Search "Experts Exchange"
WHERE = like 'experts exchange' OR like 'experts' OR like 'exchange'
What is the best practice for this (speed etc)? Im going to take out all stop words so I just get a search on only the keywords typed.
Regs,
Numb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Standard edition comes with Full Text indexing, I can't remember if the default install options have it turned on, however, you just need to install it and turn it on. I don't have much experience in it, but from what I read, it looks easy to configure, and quite robust.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is the split function used .
Raj
Raj
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
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(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
ASKER
So I kinda need some kind of split function or something. However, I am going to get into a heavy discussion with our hosts as well!!!