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:
s
elect * 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:
s
elect * from publications where FIND_IN_SET('1',authoridlist);or using REGEXP:
s
elect * 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