Query to return record only if search text is found as whole word

With the where condition

like '%hell%'

records with 'hello', 'shell', etc will be returned in addition to
records where 'hell' is found as a word by itself.

How do I write a condition that will ONLY return records where 'hell'
is found as a word by itself?

Regards,

Jan Nordgreen

damezumari wrote:

> With the where condition
>
> like '%hell%'
>
> records with 'hello', 'shell', etc will be returned in addition to
> records where 'hell' is found as a word by itself.
>
> How do I write a condition that will ONLY return records where 'hell'
> is found as a word by itself?
>


Either use a FULLTEXT index or a regex that looks for word boundaries (very
slow).


--
Brian Wakem

On Sun, 21 Oct 2007 03:58:01 -0700, damezumari
<jannordgreen@gmail.com> wrote:

>With the where condition
>
>like '%hell%'
>
>records with 'hello', 'shell', etc will be returned in addition to
>records where 'hell' is found as a word by itself.
>
>How do I write a condition that will ONLY return records where 'hell'
>is found as a word by itself?

LIKE '% hell %'

Ok, this misses occurences with punctation instead of
spaces.

LIKE '% hell %' OR LIKE '% hell.%' OR LIKE '% hell,%'
helps a lot, but might not be good enough for your
purpose. Then it's time to go try FULL TEXT indexes, like
Brian suggests.

>Regards,
>
>Jan Nordgreen
--
( Kees
)
c[_] I don't want to be your other half.
I believe that One and One make TWO.
(Alanis Morrisette: "Not the Doctor") (#185)

Kees Nuyt wrote:
> On Sun, 21 Oct 2007 03:58:01 -0700, damezumari
> <jannordgreen@gmail.com> wrote:
>
>> With the where condition
>>
>> like '%hell%'
>>
>> records with 'hello', 'shell', etc will be returned in addition to
>> records where 'hell' is found as a word by itself.
>>
>> How do I write a condition that will ONLY return records where
>> 'hell' is found as a word by itself?
>
> LIKE '% hell %'
>
> Ok, this misses occurences with punctation instead of
> spaces.
>
> LIKE '% hell %' OR LIKE '% hell.%' OR LIKE '% hell,%'
> helps a lot, but might not be good enough for your
> purpose. Then it's time to go try FULL TEXT indexes, like
> Brian suggests.
Of course, they miss hell at the beginning or end of a field and so won't
find:
"Hello I must be going" (as said by Groucho Marx)

On Sun, 21 Oct 2007 16:13:18 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>Kees Nuyt wrote:
>> On Sun, 21 Oct 2007 03:58:01 -0700, damezumari
>> <jannordgreen@gmail.com> wrote:
>>
>>> With the where condition
>>>
>>> like '%hell%'
>>>
>>> records with 'hello', 'shell', etc will be returned in addition to
>>> records where 'hell' is found as a word by itself.
>>>
>>> How do I write a condition that will ONLY return records where
>>> 'hell' is found as a word by itself?
>>
>> LIKE '% hell %'
>>
>> Ok, this misses occurences with punctation instead of
>> spaces.
>>
>> LIKE '% hell %' OR LIKE '% hell.%' OR LIKE '% hell,%'
>> helps a lot, but might not be good enough for your
>> purpose. Then it's time to go try FULL TEXT indexes, like
>> Brian suggests.
>Of course, they miss hell at the beginning or end of a field and so won't
>find:
>"Hello I must be going" (as said by Groucho Marx)

You're right, so we'll make it:

x LIKE 'hell %' OR x LIKE '% hell' OR
x LIKE '% hell %' OR x LIKE '% hell.%' OR
x LIKE '% hell,%'

FULL TEXT indexes are getting more attractive ;)
--
( Kees
)
c[_] ...universities truly are storehouses of knowledge: students arrive
from school confident that they know very nearly everything, and
they leave years later certain that they know practically nothing.
Where did all the knowledge go in the meantime? Into the
university, of course, where it is carefully dried and stored.
(Terry Pratchett, Ian Stewart and Jack Cohen) (#97)

Kees Nuyt wrote:
> On Sun, 21 Oct 2007 16:13:18 +0100, "Paul Lautman"
> <paul.lautman@btinternet.com> wrote:
>
>> Kees Nuyt wrote:
>>> On Sun, 21 Oct 2007 03:58:01 -0700, damezumari
>>> <jannordgreen@gmail.com> wrote:
>>>
>>>> With the where condition
>>>>
>>>> like '%hell%'
>>>>
>>>> records with 'hello', 'shell', etc will be returned in addition to
>>>> records where 'hell' is found as a word by itself.
>>>>
>>>> How do I write a condition that will ONLY return records where
>>>> 'hell' is found as a word by itself?
>>>
>>> LIKE '% hell %'
>>>
>>> Ok, this misses occurences with punctation instead of
>>> spaces.
>>>
>>> LIKE '% hell %' OR LIKE '% hell.%' OR LIKE '% hell,%'
>>> helps a lot, but might not be good enough for your
>>> purpose. Then it's time to go try FULL TEXT indexes, like
>>> Brian suggests.
>> Of course, they miss hell at the beginning or end of a field and so
>> won't find:
>> "Hello I must be going" (as said by Groucho Marx)
>
> You're right, so we'll make it:
>
> x LIKE 'hell %' OR x LIKE '% hell' OR
> x LIKE '% hell %' OR x LIKE '% hell.%' OR
> x LIKE '% hell,%'
>
> FULL TEXT indexes are getting more attractive ;)

ahh, but that means that you will get "shell" and "hello", when the OP
wanted only things like:
"Hell is very cold tonight.

Bring on the FULLTEXT

Thanks for enlightening me on fulltext indexing! and why there is no
good alternative. :)

Regards,

Jan Nordgreen

On 22 Oct, 15:34, damezumari <jannordgr...@gmail.com> wrote:
> Thanks for enlightening me on fulltext indexing! and why there is no
> good alternative. :)
>
> Regards,
>
> Jan Nordgreen

What a strange question. If you build a FULLTEXT function to do a
particular job, why would you bother to build a good alternative?

On Sun, 21 Oct 2007 03:58:01 -0700, damezumari wrote:
> With the where condition
>
> like '%hell%'
>
> records with 'hello', 'shell', etc will be returned in addition to
> records where 'hell' is found as a word by itself.
>
> How do I write a condition that will ONLY return records where 'hell'
> is found as a word by itself?

WHERE my_column REGEXP '[[:<:]]hell[[:>:]]';

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

--
17. When I employ people as advisors, I will occasionally listen to their
advice.
--Peter Anspach's list of things to do as an Evil Overlord