I came up with a clunky solution: REGEXP 'lvlscore5:[6-9][0-9][0-9][0-9][0-9]'; This gives me all scores over 6000. I then add on another [0-9] and change the [6-9] to [1-9] to do 10k+ As this was a one off and only returned 6 out of thoustands of records it was good enough for a quick and dirty manual edit.
I would of course have gone the seperate table route to hold the name/value pairs but the main reason I went the string route becasue the process that read and writes the string does all the work, there should never have been a need for MySQL to know what it contains or to process it in any way. Also as there can be 20+ bits of data in this string and the table is going to be pretty huge it would have made for an even larger 2nd table. (by "huge" I mean 1million+ records) With this in mind would it still be best to have gone the 2nd table route? -- Dave On 10/12/2007, Rob Wultsch <[EMAIL PROTECTED]> wrote: > > 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. > >
