Bl248
asked on
REGEXP for MySQL to search for matching numbers in field string
I have a field called tags that contains index id's that need to search returning records that match.
Example: Field values
5
5,15
15,5,1
33,45,1
15,5,33
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?
Example: Field values
5
5,15
15,5,1
33,45,1
15,5,33
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> If I follow this correctly, it is looking for a 15 that is either the first id (no leading comma)
> or has a leading comma but then must have a trailing comma or be the last id in the field.
>
> What if the last id was 155 - would that have have matched?
No, 155 should not give a match when searching for 15 as the pattern requires either a comma after the number, or the end of the line (indicated by $)
> or has a leading comma but then must have a trailing comma or be the last id in the field.
>
> What if the last id was 155 - would that have have matched?
No, 155 should not give a match when searching for 15 as the pattern requires either a comma after the number, or the end of the line (indicated by $)
ASKER
Thanks!
ASKER
If I follow this correctly, it is looking for a 15 that is either the first id (no leading comma) or has a leading comma but then must have a trailing comma or be the last id in the field.
What if the last id was 155 - would that have have matched?