Help With Where Clause

I am creating a food database application where I allow the user to search
for food names. For this example, lets supposed that my database has the
following food names on the table.
Salt with water
Chips and salsa
Fried Chicken
Guacamole
If the user types 'sa' on the search textbox I would like to retrieve the
following records:
Salt with water
Chips and salsa
This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
the user typed 'sa ch' (this is the string 'sa' then a space and then the
string 'ch'). I would like to retrieve the records
Salt with water
Chips and salsa
Fried Chicken
I get the same records as before because I included the string 'sa' and I
get the record 'Fried Chicken' because Chicken starts with 'ch'. The problem
is that the user can have as many words as he or she pleases. It even gets
more complicated because I would like to use a store procedure on Microsoft
SQL server.
Could someone help me creating this store procedure, PLEASE! Thank you.Rene
See if this helps
CREATE TABLE #Test
(
col VARCHAR(50)
)
INSERT INTO #Test VALUES ('Salt with water')
INSERT INTO #Test VALUES ('Chips and salsa')
INSERT INTO #Test VALUES ('Fried Chicken')
INSERT INTO #Test VALUES ('Guacamole')
SELECT * FROM #Test WHERE col LIKE '%sa%'
"Rene" <nospam@.nospam.com> wrote in message
news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am creating a food database application where I allow the user to search
> for food names. For this example, lets supposed that my database has the
> following food names on the table.
> Salt with water
> Chips and salsa
> Fried Chicken
> Guacamole
> If the user types 'sa' on the search textbox I would like to retrieve the
> following records:
> Salt with water
> Chips and salsa
> This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
> the user typed 'sa ch' (this is the string 'sa' then a space and then the
> string 'ch'). I would like to retrieve the records
> Salt with water
> Chips and salsa
> Fried Chicken
> I get the same records as before because I included the string 'sa' and I
> get the record 'Fried Chicken' because Chicken starts with 'ch'. The
problem
> is that the user can have as many words as he or she pleases. It even gets
> more complicated because I would like to use a store procedure on
Microsoft
> SQL server.
> Could someone help me creating this store procedure, PLEASE! Thank you.
>|||> SELECT * FROM #Test WHERE col LIKE '%sa%'
This won't work. Aquery like this will also return food name such as 'Carne
Aa' because 'Aa' has 'sa' inside its text, I am only interested on
food that has any of its words *start* with the string 'sa'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Rene
> See if this helps
> CREATE TABLE #Test
> (
> col VARCHAR(50)
> )
> INSERT INTO #Test VALUES ('Salt with water')
> INSERT INTO #Test VALUES ('Chips and salsa')
> INSERT INTO #Test VALUES ('Fried Chicken')
> INSERT INTO #Test VALUES ('Guacamole')
> SELECT * FROM #Test WHERE col LIKE '%sa%'
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> problem
> Microsoft
>|||WHERE col LIKE '% sa%' or col like LIKE '%,sa%' or col like LIKE '%.sa%' or
left(col, 2) = 'sa'
You know where I'm going with this, right.
I have seen a script (stored procedure or function) that extracts words from
a string and converts it to a table, one word per column
This may help you with this problem. Maybe someone here could point you to
the script. I can't seem to be able to find it right now.
"Rene" <nospam@.nospam.com> wrote in message
news:ONj7qTqaFHA.2736@.TK2MSFTNGP12.phx.gbl...
> This won't work. Aquery like this will also return food name such as
> 'Carne Aa' because 'Aa' has 'sa' inside its text, I am only
> interested on food that has any of its words *start* with the string 'sa'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
>