(This is from the SQLTeam forums. I noticed a reply of mine to a user's problem might be useful to others .. so here it is!)
Here's one way to implement a search, similar to Google's, if you don't want to (or can't) use full text indexing. This allows you to pass a string of words, and indicate that either ALL words must match, ANY must match, or the exact string must match.
This is a stripped-down, highly edited version of code I wrote a while ago here at work. This will perform a search in the table "SomeTable", checking the text in the column "SomeColumn".
Use only if you are not searching lots of rows -- less than a few thousand should be fairly efficient; any more than that and you should use SQL's text indexing features.
create procedure SearchSample @SearchString varchar(200), @MatchType int = 0
as
-- SeachString: String of 1 or more search terms, all separated by spaces
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
-- Results are returned in order of relevance
declare @i1 int;
declare @i2 int;
declare @Word varchar(100);
declare @Words table (Word varchar(100) not null);
declare @WordCount as integer;
set nocount on
-- Parse the SearchString to extract all words:
if (@MatchType != 2)
begin
set @SearchString = ' ' + @SearchString + ' ';
set @i1 = 1;
while (@i1 != 0)
begin
set @i2=charindex(' ', @SearchString, @i1+1)
if (@i2 != 0)
begin
set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))
if @Word != '' insert into @Words select @Word
end
set @i1 = @i2
end
end
else
insert into @Words select ltrim(rtrim(@SearchString))
-- Get the total # of words:
set @WordCount = (select count(*) from @Words)
-- Return Results in order of relevance:
select
a.MatchPct, T.*
from
SomeTable T
inner join
(
select
ID, Count(*) * 1.0 / @WordCount as MatchPct
from
SomeTable T
inner join
@Words W on ' ' + T.SomeColumn + ' ' like '%[^a-z]' + Word + '[^a-z]%'
group by
ID
) a on T.ID = a.ID
where
MatchPct = 1 or @MatchType <>1
order by
MatchPct