REGEXP for MySQL to search for matching numbers in field string
Posted on 2010-01-11
I have a field called tags that contains index id's that need to search returning records that match.
Example: Field values
There are 3 records that match a search for 5 and 15 and the search could have been 15 and 5 but in both cases 3 records should be returned. Searching on just 5 returns 4 records. Searching on 5,15,3 should return 0 records.
Since the order can not be determined, I am using REGEXP in the MySQL query as in:
SELECT itemid FROM tagword_items WHERE tags REGEXP '15' AND tags REGEXP '3'
The issue is that this will return 1 match since the 3 in 33 is a REGEXP match and it really should not return any matches.
The comma is a delimiter and I could force the field values to have a , at the beginning and at the end and then do a REGEXP on ',15,' AND ',3,' - but is there a better way?