Incrementing A Field - [TEXT][NUMBER]

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
FROM table
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…