Search for whole words in MySQL 3.23

------=_Part_28269_2432758.1181765951960
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi all,

I'm setting up a database search on a site, where I will essentially be
BOOLEAN searching about 6 database fields. Unfortunately, the database I'm
using is MySQL version 3.23 (completely out of my control), so I cannot use
IN BOOLEAN MODE to simplify the process on my end. I've been able to put
together a function that does almost everything that I want, but I'm running
into a problem - searching for whole words that may or may not be in the
beginning or end of the database field. For example, syntax like this:

....WHERE `field` LIKE '%searchterm%'...

has the limitation that if the search term is 'car', it will also bring back
'scar', 'cartoon', etc.

While if the syntax is:

....WHERE `field` LIKE '% searchterm %'... (with spaces)

it misses words that are the first or last words of the field, or words that
are at the end of sentences, before commas, etc.

I've looked around and haven't been able to find a way to search only for
complete words within a block of text. Any help?

Thanks in advance,
Zeth

------=_Part_28269_2432758.1181765951960--

Re: Search for whole words in MySQL 3.23

I suck at regex, but looks like 3.23 may actually support it. And most regex implementations have a word boundary code for doing exactly what you're talking about.

Referring to this page: http://dev.mysql.com/doc/refman/4.1/en/regexp.html

I found this example.. hopefully it'll help you (and work ok in 3.23):

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; -> 1
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]'; -> 0


-TG

= = = Original message = = =

Hi all,

I'm setting up a database search on a site, where I will essentially be
BOOLEAN searching about 6 database fields. Unfortunately, the database I'm
using is MySQL version 3.23 (completely out of my control), so I cannot use
IN BOOLEAN MODE to simplify the process on my end. I've been able to put
together a function that does almost everything that I want, but I'm running
into a problem - searching for whole words that may or may not be in the
beginning or end of the database field. For example, syntax like this:

....WHERE `field` LIKE '%searchterm%'...

has the limitation that if the search term is 'car', it will also bring back
'scar', 'cartoon', etc.

While if the syntax is:

....WHERE `field` LIKE '% searchterm %'... (with spaces)

it misses words that are the first or last words of the field, or words that
are at the end of sentences, before commas, etc.

I've looked around and haven't been able to find a way to search only for
complete words within a block of text. Any help?

Thanks in advance,
Zeth


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search for whole words in MySQL 3.23

------=_Part_29413_15573587.1181769963707
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

TG -

This is exactly what I was looking for. Thanks a bunch.

Zeth

On 6/13/07, tg-php@gryffyndevelopment.com
wrote:
>
> I suck at regex, but looks like 3.23 may actually support it. And most
> regex implementations have a word boundary code for doing exactly what
> you're talking about.
>
> Referring to this page: http://dev.mysql.com/doc/refman/4.1/en/regexp.html
>
> I found this example.. hopefully it'll help you (and work ok in 3.23):
>
> [[:<:]], [[:>:]]
>
> These markers stand for word boundaries. They match the beginning and end
> of words, respectively. A word is a sequence of word characters that is not
> preceded by or followed by word characters. A word character is an
> alphanumeric character in the alnum class or an underscore (_).
>
> mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; -> 1
> mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]'; -> 0
>
>
> -TG
>
> = = = Original message = = =
>
> Hi all,
>
> I'm setting up a database search on a site, where I will essentially be
> BOOLEAN searching about 6 database fields. Unfortunately, the database I'm
> using is MySQL version 3.23 (completely out of my control), so I cannot
> use
> IN BOOLEAN MODE to simplify the process on my end. I've been able to put
> together a function that does almost everything that I want, but I'm
> running
> into a problem - searching for whole words that may or may not be in the
> beginning or end of the database field. For example, syntax like this:
>
> ...WHERE `field` LIKE '%searchterm%'...
>
> has the limitation that if the search term is 'car', it will also bring
> back
> 'scar', 'cartoon', etc.
>
> While if the syntax is:
>
> ...WHERE `field` LIKE '% searchterm %'... (with spaces)
>
> it misses words that are the first or last words of the field, or words
> that
> are at the end of sentences, before commas, etc.
>
> I've looked around and haven't been able to find a way to search only for
> complete words within a block of text. Any help?
>
> Thanks in advance,
> Zeth
>
>
> ___________________________________________________________
> Sent by ePrompter, the premier email notification software.
> Free download at http://www.ePrompter.com.
>
>

------=_Part_29413_15573587.1181769963707--