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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
