SEO

#1 (**permalink**) 02-28-2008, 08:16 AM

I have a query designed to search multiple fields for any word that
starts with the characters entered in a search. Each field will
typically contain more than one word. The query takes the form:

SELECT
field1, field2
FROM table1 WHERE ((field1 LIKE "a%") OR (field1 LIKE "% a%") OR (
field2 LIKE "a%") OR (field2 LIKE "% a%"))

Is there a more efficient way to search than this?

Michael

#2 (**permalink**) 02-28-2008, 08:17 AM

You can use regular expressions which would cut down your WHERE
conditions...

SELECT field1, field2
FROM table1
WHERE field1 REGEXP '(^|[[:space:]])a[[:alnum:]]+'
OR field2 REGEXP '(^|[[:space:]])a[[:alnum:]]+';

m.k.ball@btinternet.com wrote:
> I have a query designed to search multiple fields for any word that
> starts with the characters entered in a search. Each field will
> typically contain more than one word. The query takes the form:
>
> SELECT
> field1, field2
> FROM table1 WHERE ((field1 LIKE "a%") OR (field1 LIKE "% a%") OR (
> field2 LIKE "a%") OR (field2 LIKE "% a%"))
>
> Is there a more efficient way to search than this?
>
> Michael

#3 (**permalink**) 02-28-2008, 08:18 AM

> m.k.ball@btinternet.com wrote:
>> WHERE ((field1 LIKE "a%") OR (field1 LIKE "% a%") OR (
>> field2 LIKE "a%") OR (field2 LIKE "% a%"))

Here's another regular expression alternative that matches the same cases:

WHERE field1 REGEXP '[[:<:]]a' OR field2 REGEXP '[[:<:]]a'

Regards,
Bill K.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

searching across multiple fields