On Dec 9, 2007 9:17 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > > On Dec 9, 2007 10:53 AM, David Scott <[EMAIL PROTECTED]> wrote: > > I have a field in a DB that contains something like > > "lvlscore4:4493,lvlscore5:3232,lvlscore6:6128" > > I need to select all records where the "lvlscore5:" is greater than 3000. > > I think this can be done using REGEXP but I have read the docs and am non > > the wiser for it. Can anyone point me in the right direction? > > -- > > Thanks > > David Scott > > > Insert lecture about correctly normalizing data. > > I think what you suggested is the wrong path for a successful outcome. > As far as I can tell the regex engine in mysql only returns a > booleans. http://dev.mysql.com/doc/refman/5.0/en/regexp.html . Someone > correct me if I am wrong. > > This would work: > > SELECT 'lvlscore4:4493,lvlscore5:3232,lvlscore6:6128', > SUBSTRING('lvlscore4:4493,lvlscore5:3232,lvlscore6:6128', > > LOCATE('lvlscore5','lvlscore4:4493,lvlscore5:3232,lvlscore6:6128')+length('lvlscore5')+1, > 4 > > ) as 'lvlscore5' > > 4 is a magic number that will not work if the score is not always of > length 4. If that is the case replacing the last argument of substring > with a bit more logic will sort this problem. >
To be clear the regex should work, but it will probably take more work than what I just suggested. Using what I suggested in the where clause rather than as shown in the select clause will probably be the easiest solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
