[SOLVED] REGEXP problem in mysql

[KrisNz]

Hi, I have a column I'm trying to extract information from. The previous developer on a project has stored a number of ids in a text field separated by semicolons. an example of data in this field could be "1;2;3;4" or just "49" if theres only one id. Im trying to get a count of the number of times an id appears in a record for a particular user.

 

from php im running this query

select count(ID) as agent_tsr_count from TSR tsr where reaID REGEXP '149[^0-9][[.semicolon.]]{0,1}' and lawID = 31

 

This seems close but is definitely not right! Can someone please advise me on how to change the query so it only matches the whole number, optionally followed by a semi-colon?

 

Thanks for reading this,

Kris.

 

[bubblegum.anarchy]

example:

 

id => 1

reaID => '1;2;3;4;2'

 

id => 2

reaID => '1;3;2'

 

the result for reaID = 2 would be:

 

id    reaID_count

1      2

2      1

 

correct ?

[KrisNz]

Yup, thats what Im after.

 

and so if the data looked like

id => 2

reaID => '1;3;2;22;'

 

I still need the count to be 1.

 

Thanks.

[bubblegum.anarchy]

are the numbers stored: number and semicolon? or just number, then semicolon and number... though you could always concat a semicolon to all the fields if the later is evident and then search for number and semicolon.

[bubblegum.anarchy]

I can't find anyway to get a match count with REGEXP.... hmm.

[bubblegum.anarchy]

well... I can't figure a way around this problem without creating a seperate function.

[Wildbug]

mysql> SELECT '1;30;2;0' REGEXP CONCAT('[[:<:]]',2,'[[:>:]];?');                
+---------------------------------------------------+
| '1;30;2;0' REGEXP CONCAT('[[:<:]]',2,'[[:>:]];?') |
+---------------------------------------------------+
|                                                 1 | 
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '1;22;' REGEXP CONCAT('[[:<:]]',2,'[[:>:]];?');                  
+------------------------------------------------+
| '1;22;' REGEXP CONCAT('[[:<:]]',2,'[[:>:]];?') |
+------------------------------------------------+
|                                              0 | 
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

 

That pattern matches your number followed by an optional semi-colon.  It does not count how many times that particular number is within the string, however.  I think bubblegum might be right about not being able to count it in MySQL.  You could use the pattern to match rows containing that ID and pull them into PHP for a final count.

 

And since you blamed the previous developer for storing multiple records in a delimited string  , you might consider altering the database structure to a seperate table of UserID+reaID.  Just a thought -- I don't know if the difficulty thereof would outweight the improvment.

[KrisNz]

Wildbug, that was exactly what I needed. I see now that thats even in the mysql manual for regexp, I just didn't read it thoroughly enough.

 

I should apologize, when I wrote

I'm trying to get a count of the number of times an id appears in a record for a particular use

I realize now this was misleading, what I meant was that I wanted to count rows where the number appeared in that field. I've now been able to do that by working in Wildbug's regexp into my query.

 

And yes, It would be nice to be able to move those ids into a look up table, but really thats the least of this projects problems!

 

Thanks heaps to both of you! Really appreciate it.

[Wildbug]

I'm glad that's what you needed.

 

I just realized that you don't really need the ";?" part of the regular expression -- the word boundries will match regardless.