Anti-Query

[Aaron]

I'm writing a program that has an auto spelling correction feature. it is
kind of like google and ebay's spelling suggestion features (did you mean?:
correctly spelled word)


I need to write a sql query that does partial match, which is the exact
opposite as how you would do a wild card search.

Wildcard search example
---
Select id from table1 where message like 'i%like%food%'
--
it would yield results like these
"i like mexican food"
"i don't like this food"
"i like food in general"
[color=blue][color=green][color=darkred]
>>>>>>>>>>>>>>> >>>>>>>>>[/color][/color][/color]
WHAT I NEED TO DO
I need to do the exact opposite of the example above
this is what i have in the database.
ID(autonumber) keywords(varcha r)
---------------- --------------------
1 i%like%food%
2 i%like%sports%
3 i%like%computer s%
and so on


What I would like to do is to input these sentences and get an output of the
wild card.
---pseudo code---
select id from table1 where keywords like 'i like food in general'
---
results:
'i%like%food%'


i hope this makes sense

Thanks in advance,

Aaron

[William Ryan]

Re: Anti-Query

If I understand you correct... SELECT id From table1 WHERE Message
LIKE(MessageCol umn, ' ' , '%')

However, I'm not sure what this would accomplish so I am probably not
understanding the goal. If your actual query was select id from table1
where keywords like 'i like food in general'

You want it returned with % instead of spaces?
"Aaron" <kuya789@yahoo. com> wrote in message
news:%23VYWRsM3 DHA.3256@tk2msf tngp13.phx.gbl. ..[color=blue]
> I'm writing a program that has an auto spelling correction feature. it is
> kind of like google and ebay's spelling suggestion features (did you[/color]
mean?:[color=blue]
> correctly spelled word)
>
>
> I need to write a sql query that does partial match, which is the exact
> opposite as how you would do a wild card search.
>
> Wildcard search example
> ---
> Select id from table1 where message like 'i%like%food%'
> --
> it would yield results like these
> "i like mexican food"
> "i don't like this food"
> "i like food in general"
>[color=green][color=darkred]
> >>>>>>>>>>>>>>> >>>>>>>>>[/color][/color]
> WHAT I NEED TO DO
> I need to do the exact opposite of the example above
> this is what i have in the database.
> ID(autonumber) keywords(varcha r)
> ---------------- --------------------
> 1 i%like%food%
> 2 i%like%sports%
> 3 i%like%computer s%
> and so on
>
>
> What I would like to do is to input these sentences and get an output of[/color]
the[color=blue]
> wild card.
> ---pseudo code---
> select id from table1 where keywords like 'i like food in general'
> ---
> results:
> 'i%like%food%'
>
>
> i hope this makes sense
>
> Thanks in advance,
>
> Aaron
>
>[/color]

[Aaron]

Re: Anti-Query

No that was not the query, it was only a example, everything after the WHERE
clause is fake.

say the user type in 'i like food in general'. the search engine would look
for this string a the keyword column.

the keyword column has records like

'a%apple%'
'a%airpline%'
'i%like%food%'

it would return 'i%like%food%' as result.

Thanks


"William Ryan" <dotnetguru@nos pam.comcast.net > wrote in message
news:OTAa1CN3DH A.1804@TK2MSFTN GP12.phx.gbl...[color=blue]
> If I understand you correct... SELECT id From table1 WHERE Message
> LIKE(MessageCol umn, ' ' , '%')
>
> However, I'm not sure what this would accomplish so I am probably not
> understanding the goal. If your actual query was select id from table1
> where keywords like 'i like food in general'
>
> You want it returned with % instead of spaces?
> "Aaron" <kuya789@yahoo. com> wrote in message
> news:%23VYWRsM3 DHA.3256@tk2msf tngp13.phx.gbl. ..[color=green]
> > I'm writing a program that has an auto spelling correction feature. it[/color][/color]
is[color=blue][color=green]
> > kind of like google and ebay's spelling suggestion features (did you[/color]
> mean?:[color=green]
> > correctly spelled word)
> >
> >
> > I need to write a sql query that does partial match, which is the exact
> > opposite as how you would do a wild card search.
> >
> > Wildcard search example
> > ---
> > Select id from table1 where message like 'i%like%food%'
> > --
> > it would yield results like these
> > "i like mexican food"
> > "i don't like this food"
> > "i like food in general"
> >[color=darkred]
> > >>>>>>>>>>>>>>> >>>>>>>>>[/color]
> > WHAT I NEED TO DO
> > I need to do the exact opposite of the example above
> > this is what i have in the database.
> > ID(autonumber) keywords(varcha r)
> > ---------------- --------------------
> > 1 i%like%food%
> > 2 i%like%sports%
> > 3 i%like%computer s%
> > and so on
> >
> >
> > What I would like to do is to input these sentences and get an output of[/color]
> the[color=green]
> > wild card.
> > ---pseudo code---
> > select id from table1 where keywords like 'i like food in general'
> > ---
> > results:
> > 'i%like%food%'
> >
> >
> > i hope this makes sense
> >
> > Thanks in advance,
> >
> > Aaron
> >
> >[/color]
>
>[/color]

[Garrett]

Re: Anti-Query

Try the following.

The table:

select * from matchpats

tabid keywords
----------- ------------------------------
1 a%apple%
2 a%airplane%
3 i%like%food%

(3 row(s) affected)

=============== =============== =====
The code:

declare @message as char(50)

set @message = 'i like food in general'

select *
from matchpats
where @message like rtrim(keywords)
=============== =============== ======
results:

tabid keywords
----------- ------------------------------
3 i%like%food%

(1 row(s) affected)
=============== =============== =======
Is this the sort of thing you wanted?

There may be issues with trailing blanks.
I ran the code in the Query Analyzer.


"Aaron" <kuya789@yahoo. com> wrote in message
news:#aLckUN3DH A.1052@TK2MSFTN GP12.phx.gbl...[color=blue]
> No that was not the query, it was only a example, everything after the[/color]
WHERE[color=blue]
> clause is fake.
>
> say the user type in 'i like food in general'. the search engine would[/color]
look[color=blue]
> for this string a the keyword column.
>
> the keyword column has records like
>
> 'a%apple%'
> 'a%airpline%'
> 'i%like%food%'
>
> it would return 'i%like%food%' as result.
>
> Thanks
>
>
> "William Ryan" <dotnetguru@nos pam.comcast.net > wrote in message
> news:OTAa1CN3DH A.1804@TK2MSFTN GP12.phx.gbl...[color=green]
> > If I understand you correct... SELECT id From table1 WHERE Message
> > LIKE(MessageCol umn, ' ' , '%')
> >
> > However, I'm not sure what this would accomplish so I am probably not
> > understanding the goal. If your actual query was select id from table1
> > where keywords like 'i like food in general'
> >
> > You want it returned with % instead of spaces?
> > "Aaron" <kuya789@yahoo. com> wrote in message
> > news:%23VYWRsM3 DHA.3256@tk2msf tngp13.phx.gbl. ..[color=darkred]
> > > I'm writing a program that has an auto spelling correction feature. it[/color][/color]
> is[color=green][color=darkred]
> > > kind of like google and ebay's spelling suggestion features (did you[/color]
> > mean?:[color=darkred]
> > > correctly spelled word)
> > >
> > >
> > > I need to write a sql query that does partial match, which is the[/color][/color][/color]
exact[color=blue][color=green][color=darkred]
> > > opposite as how you would do a wild card search.
> > >
> > > Wildcard search example
> > > ---
> > > Select id from table1 where message like 'i%like%food%'
> > > --
> > > it would yield results like these
> > > "i like mexican food"
> > > "i don't like this food"
> > > "i like food in general"
> > >
> > > >>>>>>>>>>>>>>> >>>>>>>>>
> > > WHAT I NEED TO DO
> > > I need to do the exact opposite of the example above
> > > this is what i have in the database.
> > > ID(autonumber) keywords(varcha r)
> > > ---------------- --------------------
> > > 1 i%like%food%
> > > 2 i%like%sports%
> > > 3 i%like%computer s%
> > > and so on
> > >
> > >
> > > What I would like to do is to input these sentences and get an output[/color][/color][/color]
of[color=blue][color=green]
> > the[color=darkred]
> > > wild card.
> > > ---pseudo code---
> > > select id from table1 where keywords like 'i like food in general'
> > > ---
> > > results:
> > > 'i%like%food%'
> > >
> > >
> > > i hope this makes sense
> > >
> > > Thanks in advance,
> > >
> > > Aaron
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]