1) Where is the DDL?
2) Why would you do this in SQL?
3) There are many cheap or free text search packages that will handle
this type of problem much better.

Here is something I tossed together to illustrate how you might implement a
thesaurus and noise keyword table. It is functional and references the
ProductDescription table in the AdventureWorks sample database.

declare
    @userinput    as varchar(8000),    -- Input parameter
    @i            as int,
    @w            as varchar(8000)

declare    @include table (keyword varchar(8000))
declare    @exclude table (keyword varchar(8000))

declare    @thesaurus table (input varchar(8000), keyword varchar(8000))
insert into @thesaurus (input,keyword) values ('offroad','off-road')
insert into @thesaurus (input,keyword) values ('superior','super')

declare    @noise table (keyword varchar(8000))
insert into @noise (keyword) values ('and')
insert into @noise (keyword) values ('with')
insert into @noise (keyword) values ('for')

select
    @userinput = '+superior;+offroad;+bike;+for;+racing;-aluminum;',
    @i = 0,
    @w = ''

-- Parse the string of keywords into two tables; one to include and one to
exclude:
while @i < len(@userinput)
begin
    select @i = @i + 1
    if substring(@userinput,@i,1) <> ';'
    begin
        select @w = @w + substring(@userinput,@i,1)
    end
    else
    begin
        if left(@w,1) = '+' insert into @include (keyword) values
(substring(@w,2,8000))
        if left(@w,1) = '-' insert into @exclude (keyword) values
(substring(@w,2,8000))
        select @w = ''
    end
end

-- Remove noise keywords:
delete from @include where keyword in (select keyword from @noise)
delete from @exclude where keyword in (select keyword from @noise)

-- Substitute keywords with thesausus equivalent:
update
    @include
set
    keyword = T.keyword
from @include as K
    join @thesaurus as T
        on T.input = K.keyword

update
    @exclude
set
    keyword = T.keyword
from @exclude as K
    join @thesaurus as T
        on T.input = K.keyword

-- Select contents of included and excluded keywords:
select '@include' as K, * from @include
select '@exclude' as K, * from @exclude

-- Query the [ProductDescription] table:
select
    P.ProductDescriptionID,
    P.Description,
    count(*) as Hits
from AdventureWorks.Production.ProductDescription as P
    join @include as I
        on P.Description like '%' + I.keyword + '%'
    left join @exclude as E
        on P.Description like '%' + E.keyword + '%'
where
    E.keyword is null
group by
    P.ProductDescriptionID,
    P.Description

K    keyword
@include    super
@include    off-road
@include    bike
@include    racing

K    keyword
@exclude    aluminum

ProductDescriptionID    Description    Hits
8    Suitable for any type of riding, on or off-road. Fits any budget.
Smooth-shifting with a comfortable ride.    1
64    This bike delivers a high-level of performance on a budget. It is
responsive and maneuverable, and offers peace-of-mind when you decide to go
off-road.    2
88    For true trail addicts.  An extremely durable bike that will go anywhere
and keep you in control on challenging terrain - without breaking your
budget.    1
168    Top-of-the-line competition mountain bike. Performance-enhancing options
include the innovative HL Frame, super-smooth front suspension, and traction
for all terrain.    2
170    Suitable for any type of off-road trip. Fits any budget.    1
209    Entry level adult bike; offers a comfortable ride cross-country or down
the block. Quick-release hubs and rims.    1
249    Value-priced bike with many features of our top-of-the-line models. Has
the same light, stiff frame, and the quick acceleration we're famous for.    1
320    Same technology as all of our Road series bikes, but the frame is sized
for a woman.  Perfect all-around bike for road or racing.    2
321    Same technology as all of our Road series bikes.  Perfect all-around
bike for road or racing.    2
337    A true multi-sport bike that offers streamlined riding and a
revolutionary design. Aerodynamic design lets you ride with the pros, and the
gearing will conquer hilly roads.    1
375    Cross-train, race, or just socialize on a sleek, aerodynamic bike.  
Advanced seat technology provides comfort all day.    1
376    Cross-train, race, or just socialize on a sleek, aerodynamic bike
designed for a woman.  Advanced seat technology provides comfort all day.    1
409    Alluminum-alloy frame provides a light, stiff ride, whether you are
racing in the velodrome or on a demanding club ride on country roads.    1
513    All-occasion value bike with our basic comfort and safety features.
Offers wider, more stable tires for a ride around town or weekend trip.    1
554    The plush custom saddle keeps you riding all day,  and there's plenty of
space to add panniers and bike bags to the newly-redesigned carrier.  This
bike has stability when fully-loaded.    1
613    Superior shifting performance.    1
618    Super rigid spindle.    1
627    All-weather brake pads; provides superior stopping by applying more
surface to the rim.    1
697    All-purpose bar for on or off-road.    1
890    Lightweight kevlar racing saddle. Leather.    1
1187    Carries 4 bikes securely; steel construction, fits 2" receiver hitch.    1
1188    Clip-on fenders fit most mountain bikes.    1
1200    Men's 8-panel racing shorts - lycra with an elastic waistband and leg
grippers.    1
1201    Perfect all-purpose bike stand for working on your bike at home.
Quick-adjusting clamps and steel construction.    1
1205    Short sleeve classic breathable jersey with superior moisture control,
front zipper, and 3 back pockets.    1

(25 row(s) affected)

The following function will take a string of words and split them into
a table...  the only caveat is that you'll need to create some logic
(either in the function or elsewhere) to remove any commas etc. that
the user might input.

create function fn_SplitList ( @List varchar(8000) )
returns @ReturnTable table
  (
        value varchar(100)
  )
as

begin
    declare @current varchar(100)

    while ( charindex(' ', @List, 1) > 0 )
    begin
        if (charindex(' ', @List, 1) > 0 )
        begin
            set @current = SUBSTRING( @List, 1, charindex(' ', @List, 1) -1 )
            insert @ReturnTable ( Value ) values ( @current )
        end
        set @List = SUBSTRING( @List, charindex(' ', @List, 1) + 1,
LEN(@List) )
    end
    insert @ReturnTable ( Value ) values ( @List )
    return
end

Once you have this, you can do something like the following to get
your result set.  I wouldn't bother worrying about removing the noise
words; a word either relates to your list or it does not.  If it does
you'll get a hit and if it doesn't you won't.

declare @products table (product_name varchar(20))
declare @thesaurus table (product_name varchar(20), synonyms
varchar(50))

declare @searchTerm varchar(50)
set @searchTerm = 'some kind of gel stuff'

set nocount on
insert @products values ('lamp')
insert @products values ('gaffe')
insert @products values ('filter')
insert @products values ('string')

insert @thesaurus values ('lamp', 'light bulb')
insert @thesaurus values ('gaffe', 'gaffer giraffe')
insert @thesaurus values ('filter', 'gel goop')
insert @thesaurus values ('string', 'rope yarn filament')
set nocount off

select
    p.product_name
from
    @products p
    inner join @thesaurus t on p.product_name = t.product_name
    inner join dbo.fn_splitList( @searchTerm ) s on t.synonyms like
'%'+s.value+'%'                                        or t.product_name like
'%'+s.value+'%'

This morning I found this:
http://www.dbforums.com/archive/index.php/t-329694.html

It showed an example of how to remove noise words from a string. I was
able to create this from it:

[code]CREATE function [dbo].[fn_thesaurus](@str varchar(200))
returns varchar(200) as begin

declare @txt varchar(200) declare
@t table(word varchar(50))

--Split search text into words - space separator
insert @t(word) select value from
dbo.fn_Split(@str,' ')

--insert thesaurus words
insert @t(word) select synonyms from tblThesaurus inner join @t on word
= tblThesaurus.product_name

--remove original word that matached the thesaurus word
delete from @t from @t t join
tblThesaurus s (nolock) on t.word = s.product_name

--remove noise words by joining to noise table
delete from @t from @t t join
tblNoise s (nolock) on t.word=s.noiseword

--reassemble string
SELECT @txt = COALESCE(@txt +' ','') + word FROM @t SELECT @txt
=@txt

return @txt end[/code]

It takes the string, adds matching thesaurus words, removes the matched
word, and removes any noise words.

My main search function will add the quotes and "AND" depending on the
search being carried out. This was simpler than I imagined, but since I
have never create a function before I thought it was going to be much
harder.

Steve