Incrementing A Field - [TEXT][NUMBER]

Databases
1

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?

2

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%'
3

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).

4

i would use an auto_increment number, and append the string prefix when retrieving rows

~so~ much simpler…

:slight_smile: