Lets say I have a database field that stores a string (varchar 25 for example). The format of the data in this field is [TEXT][NUMBER] so for example the data there might be:
What I want to do is get the highest number used, incremented it by one and then insert a new record... so in the example above, I'd want to pull out CreedFeed11, increment 11 to 12, and then insert CreedFeed12.
Is there a way to query the table to pull that highest used number? Assume in this case that there's no correlation between the highest number used in the string and an auto-incremented id field (the highest number may not always be the most recently added data in the table).
I first thought I could use MAX() and a regular expression but once the numbers went past 9 it does not work:
SELECT MAX(field) AS maxfield FROM table WHERE maxfield REGEXP '^CreedFeed[0-9]*'
Well I came up with the following solution, but I'm not sure if it's the most efficient or not. Anyone?
SELECT MAX( CAST( REPLACE(field, 'CreedFeed', '') AS UNSIGNED) ) as maxnum
WHERE field LIKE 'CreedFeed%'
I would use SUBSTRING(field, 10) instead of REPLACE(FIELD, 'CreedFeed', ''), but the rest looks good (or at least probably as good as it gets with this setup).
i would use an auto_increment number, and append the string prefix when retrieving rows
~so~ much simpler...