Auto increment a varchar in MySQL

[Ramya Raghupatruni]

I'm trying to auto increment a varchar value. I'm getting as an exception. I want to do without triggers,procedures and function.
So any suggestions are welcome.
insert into idsdf(name,sid) values("JKL",concat("JKL",convert(id,char)));
The output is like this
id(auto_increment) name sid
1 JKL JKL0

i want sid value as JKL1.

Please help me.
Thanks in advance..

[Paul Clapham]

It's pretty clear from the MySQL documentation that you can only use numeric columns as auto-incremented values. In fact if you poke around a bit you'll find this statement:

MySQL wrote:AUTO_INCREMENT applies only to integer and floating-point types.

So your hopes of no extra coding aren't going to come true.

If it was me I would aim for a solution where you had a proper auto-increment column and then build your "JKL1" value later. However I have no idea what your requirements are because you asked a question about a proposed solution and not a question about your actual problem. If you'd like to explain your requirements you might well get suggestions which solve your problem better than the idea you proposed here, I think.

[Sherin Mathew]

You can try this formula in the create table query:

create table idsdf( id int Identity(1,1) NOT NULL, name varchar(50) NOT NULL, sid as ([name ]+RIGHT('0'+ CAST(id as varchar(1)),1)) persisted, constraint[pk_id] primary key ([id] asc) );

So whenever you insert data, sid column will be in required format.

insert into employee (name) values ('jkl')

[Tim Holloway]

Paul Clapham wrote:However I have no idea what your requirements are because you asked a question about a proposed solution and not a question about your actual problem. If you'd like to explain your requirements you might well get suggestions which solve your problem better than the idea you proposed here, I think.

How many blind alleys have we all gone down be cause we did exactly this? :)