(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