Increase Alphanumeric VARCHAR Entry by Value 1?

[equipment]

On an old project because of not thought through design I have a column which actually should be set to auto_increment, though it cannot be because it are alphanumeric entries as follows:

 

c01

c02

c03

 

Now I need a way to imitate the auto_increment functionality with the SQL statement myself, my own attempt has gotten as far as the following:

 

INSERT INTO tags (tag_id, tag_name, tag_description, added_by_user_id, creation_date, last_edited) VALUES (SELECT(MAX(tag_id)+1), 

'Love', 'All about love', 7, now(), 0);

 

This one does not work as is, though the idea was to select the highest entry in the column "tag_id" and then simply increase it by the value 1.

 

Any ideas how to accomplish this?

 

 

By the way I am also not sure if you simply can increase an alphanumeric entry through this way, though I know it can be done, I just don't know how.

[requinix]

What would come after "c99"?

[equipment]

It can be c100 and further, because the c, which initially was a sign for "category" has by now no meaning anymore, as said it happened in the past, but taking it out of the system requires over hauling everything, and that is really not necessary. I prefer a workaround in this case.

[requinix]

Make a MySQL function that looks something like

SELECT @i = SUBSTRING(tag_id, 2) FROM tags ORDER BY LEN(tag_id) DESC, tag_id DESC LIMIT 1;
RETURN CONCAT("c", @i + 1);

If you need the number padded to at least two digits then throw in an LPAD.

 

Be sure to use a transaction in case two INSERTs try to run at the same time.

[fenway]

Are the letters that appear fixed?