?
Solved

REGEXP for MySQL to search for matching numbers in field string

Posted on 2010-01-11
4
Medium Priority
?
437 Views
Last Modified: 2012-05-08
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?


0
Comment
Question by:Bl248
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 2000 total points
ID: 26288158
Try this syntax:

   SELECT itemid FROM tagword_items WHERE tags REGEXP '(^|,)15(,|$)' AND tags REGEXP '(^|,)3(,|$)'
0
 

Author Closing Comment

by:Bl248
ID: 31675763
Thanks very much for the quick answer to my question.

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?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 26288628
> 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 $)

0
 

Author Comment

by:Bl248
ID: 26288658
Thanks!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question