5

I have a $string variable, and I use

 SELECT * FROM db WHERE description LIKE '%$string%' OR headline LIKE '%$string%'

As seen, I want to search the two fields "description" and "headline" to see if the string variable matches any of them.

Problem is that I want it to match whole words!!!

Ex: If description contains "hello", It is enough if $string is an 'h'. this is not what I want.It has to match the whole word only!

I split the querystring into words for this? or what?

Gowri
  • 16,587
  • 26
  • 100
  • 160

2 Answers2

13

An alternative to full text searching, which may be sufficient, is to use a REGEXP function.

Your example query might then be:

SELECT *
  FROM db
 WHERE description REGEXP '[[:<:]]$string[[:>:]]' = 1
    OR headline REGEXP '[[:<:]]$string[[:>:]]' = 1

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

martin clayton
  • 76,436
  • 32
  • 213
  • 198
  • 2
    Note that `REGEXP` is pretty inefficient for larger sets of data because MySQL can't take advantage of indexing. – Corey Ballou Feb 22 '13 at 16:44
  • To clarify, it can't take advantage of indexing for `LIKE '%term%'` either which is what the OP already had. However it *could* with `MATCH() ... AGAINST()` if there's a FULLTEXT index. – thomasrutter Apr 02 '15 at 04:33
9

If you want full word matching you should consider trying FULLTEXT searching. One prerequisite is that your table must be using the MyISAM engine:

CREATE TABLE test (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  headline VARCHAR(120) NOT NULL,
  description VARCHAR(255) NOT NULL,
  FULLTEXT(headline, description)
) ENGINE=MyISAM;

You would query for matches like so:

SELECT *
FROM test
WHERE MATCH (headline,description) AGAINST('$string');

This has the added benefit of ordering your results by relevancy.

Corey Ballou
  • 42,389
  • 8
  • 62
  • 75