Thursday, January 9, 2014

Search a word or a string in MySQL

We often perform searching a word or a string in MySQL. For example, we want to search Bowen from column authorlist, secondaryauthorlist and seriesauthorlist in table publications, we can use
SELECT * FROM publications WHERE  (CONCAT_WS(' ', authorlist, secondaryauthorlist, seriesauthorlist) LIKE '%Bowen%';
SELECT * FROM publications WHERE CONCAT_WS(' ', authorlist, secondaryauthorlist, seriesauthorlist) REGEXP '[[:<:]]Bowen[[:>:]]';

There is a small difference between these two queries. The first one matches Bowen, CBowen, CBowenm ..., the second one matches only Bowen, more explanation:

 MySQL function CONCAT_WS joins strings with separator.
CONCAT_WS(separator, string1, string2)
LIKE '%Bowen%': find string containing Bowen, which can be in the middle of a string
REGEXP: regular expression
[[:<:]] match beginning of  the word
 [[:>:]] match end of  the word
 [[:<:]]Bowen [[:>:]]  match a word boundary at the beginning and  end of a word,
i.e. BBowenn will not match, while LIKE '%Bowen%' match

No comments:

Post a Comment