Searching mySQL DB contents with LIKE

Using [ ], % and LIKE

[mcfly]

Hi everyone,

I'm implementing a simple internal search for a site that will select articles containing specified words from a database. However, I am finding LIKE to be somewhat limiting.

eg. SELECT title FROM articles WHERE content LIKE '%test%'
- this returns all articles containing words test, testing, testosterone etc. Not what is required.

eg. SELECT title FROM articles WHERE content LIKE '% test %'
- this returns only articles containing the word test.

However, the latter will not pick up on the word 'test' at the very start or very beginning of the field, or when immediately prefixed or suffixed by some punctuation.

I've found that using
SELECT ..blah .. LIKE '%[^a-z]test[^a-z]%'
should overcome the problem of having punctuation on either side of the search term.

I've had no success with this however, and it still doesn't get around the problem of the word occuring at the start or end of a field. Does anyone have any suggestions or experience in searching for words in a DB in such a way?

Thanks in advance.

[DanA]

I thought you could try
WHERE ((content LIKE '%[^a-z]test[^a-z]%') OR (content LIKE 'test[^a-z]%') OR (content LIKE '%[^a-z]test'))
but it doesn't work
Sorry

[coopster]

You may want to have a look at the REGEXP String Comparison function [mysql.com].

[DrDoc]

And, for your information -- [^a-z] requires a non a-z character on both sides of the string. You may want to work out a regexp that will allow beginning or end of string as well ;)

In fact, % also requires a character...

[mcfly]

Thanks for your input guys. It looks like coopster's suggestion of using more advanced regular expressions is the way to go.

Cheers :)