Searching without Verity

databases

Sometimes you need to search a database query without using verity. In general Verity should be used when possible, because it will yeild much better results, at better performance than the solution I'm about to show. But there are reasons for not using verity, for instance compatibility with BlueDragon, a highly customized search query, or a shared host that doesn't allow it.

Single keyword searches are easy using the LIKE operator in SQL WHERE column LIKE '%something%'. If you need to search using multiple keywords, its a harder. Here's an exmple

 SELECT stuff FROM table
 WHERE column LIKE '%#Replace(Trim(q), " ", "%' OR column  LIKE '%", "ALL")#%'

Here column would be the database field your searching and q would be your search string. Basically that code replaces all spaces with:

%' OR column like '%

So if your search string is "Monday Tuesday" the resulting query would be:

WHERE column LIKE '%Monday%' OR column LIKE '%Tuesday%'

You can ofcourse easily replace the OR with an AND, or use a variable.


1 person found this page useful, what do you think?

Trackbacks

Trackback Address: 34/EFD484AAC3D1C80F3428A9D40C07AA2F

Comments

On 12/11/2003 at 11:06:31 AM EST Chris Blackwell wrote:
1
Depending on the situation whole word searchs can offer more logical results. If your trying to match a whole word the following regex useful in MySQL.

SELECT text FROM table WHERE text REGEXP '[[:<:]]word[[:>:]]';

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?