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:
CreedFeed1
CreedFeed2
…
CreedFeed9
CreedFeed10
CreedFeed11
…
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]*'
Ideas?