On Dec 10, 2007 1:26 AM, David Scott <[EMAIL PROTECTED]> wrote:
> 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?
Your needs may vary, but I am paranoid and assume that my code is
buggy, and think it is easier to check sanity by viewing the actual
values with an intermediate value showing what had been pulled out,
and a boolean that describes a potential where clause action. Whether
or not a second table is created for sanity testing, and/or future
work I think is a matter of preference. Of course if you needed to
anything more than a single action it would almost certainly be more
efficent to create the second table. With any regex or
SUBSTRING+LOCATE solution every row would need to be examined, while
with a second table a index could be added which would make all
actions after a insert select much faster....
FYI:
single regex syntax would be REGEXP
'lvlscore5:([6-9][0-9]{3}|[0-9]{5,})' which translates to 'lvlscore5:'
followed by EITHER a number between 6 and 9 then followed by exactly 3
more numbers of any value, OR any 5 or more digit number.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
