Friday, January 24, 2014

MySQL query -find match strings using like, FIND_IN_SET and REGEXP

I have a publication database. authors are  separated as comma in id i,.e authoridlist column is
1,2,10,20. Using like in the query will cause the matching strings inaccuracy.
For example:
select * from publications where authoridlist like '%1%';
will find the author id is not only 1, but also 10 as % in MySQl query means  any number of characters.

To exactly find the substring  or number in  comma separated strings, we can use FIND_IN_SET:
select * from publications where FIND_IN_SET('1',authoridlist);
 or using REGEXP:
select * from publications where authoridlist REGEXP '[[:<:]]1[[:>:]]';
REGEXP: regular expression
[[:<:]] match beginning of  the word
 [[:>:]] match end of  the word
 [[:<:]]1 [[:>:]]  match a word boundary at the beginning and  end of a word, here the word i s 1.

No comments:

Post a Comment