Re: SQL Regular Expression Question

On Sun, 22 Jul 2001, Lee Harr wrote:

> On 20 Jul 2001 13:36:35 -0700, Blake <blake@shopwhatcom.com> wrote:
> > Wondering if someone could give me some guidance on this situation? I
> > have a table column name "question" it contains a question, usally
> > over 10 words. What I would like to do is give a user an option for
> > searching the question column. I successfully, in PHP and Postgresql
> > written a select statement to match a single word in the "question"
> > column string: (its not very rodust)
> >
> > $query = pg_Exec($conn, "SELECT FROM faq WHERE question ~*
> > 'OneKeyword'");
> >
> > How can I write a SQL Regualr Expression to match multiple "User
> > Submitted" keywords and get a count on how many matched from the
> > select statement??
>
>
> Don't know if this helps or not, but how about:
>
> SELECT * FROM faq WHERE question ~* 'OneKeyword' OR question ~* 'TwoKeyword'
> or
> SELECT * FROM faq WHERE question ~* 'OneKeyword' AND question ~* 'TwoKeyword'
>
> or
>
> SELECT count(*) FROM faq ...
>
> Seems like with PHP you could build up the query string with ANDs or
> ORs (depending on how you want it to work) and then submit the query.

You might want to consider if you *want* to take what they give you and
use it as a regular expression. They might include symbols that have
special meanings, and then wouldn't get the results they'd expect. Or,
worse, they might write a regular expression that takes extraordinary
amount of processing power to check (see Freidl, O'Reilly, for examples.)

A better idea might be to use ILIKE, the case-insensitive simple pattern
matching operator. This would look like:

  where question ILIKE '%keyword%' or '%keyword%'

The speed should be consistent (or perhaps even better) than full regexes,
and you avoid the security/weird results problems of regexes.

hth,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington