How to auto increment an alphanumeric Primary Key in SQL? :(

How to auto increment an alphanumeric Primary Key in SQL? :( Because I want to add something like this in the Primary Key, for example i'll add a new data with an alphanumeric value of ABC-0001, then I'll add another 1, and it auto increments to ABC-0002 and so on.. How can I do it? And if I'll add a new alpha character in the Primary Key field, for example DEF-0001, then I'll add another and it auto increments to 002, and so on, and it will go back to 0001 if i'll use another combination of alpha characters. for example, i'll add a new alpha character AAA, will it go back to 0001 or it will continue?

T___T I hope u get my point.. I want my table to look like this if i have added the data

ABC-0001
ABC-0002
DEF-0001
DEF-0002
AAA-0001

then if il add a new 1, for example ABC

it will auto increment to 0003 in the same field, it will look like this after adding

ABC-0001
ABC-0002
ABC-0003
DEF-0001
DEF-0002
AAA-0001

Will it be possible? :(

hi,

i got wat you wanted to say.but i think auotincrement is possible only with numeric fields,i cant think of any possible algorithm that would solve your problem for autoincrementing alphanumeric field.But one possible solution would be to generate your own primary key.For that to happen first you must split your Alpha-numeric field into two..ie first part will take ABC and second would take ur numeric field 0001 etc...

make your primary key as the combination of both the fields...this is the solution i could think of...

hope you could find,wat you actually wanted..all the best :)

|||

Hey ranjit that's what im thinking off also... bat what if there's a new alpha combination, for example DEF, it will continue to autoincrement, and i think it will not return to 0001.. so what will happen is, if i have added ABC-0001 and ABC-0002, the next one would be
DEF-0003 not DEF-0001

will it be possible to put it back to 0001 if il add a new alpha combination? or very impossible? :(

T___T

|||

to get the primary key... you have to findmax for that perticular string with Query sequence like...

SELECT MAX(CAST(SUBSTRING(id,CHARINDEX('-',id)+1,50) AS INT)) + 1 FROM TBL_Table WHEREID LIKE 'DEF%' [this can be like ..."id like 'ABC%' to get highest value in ABC Sequence].. hope it helps you..

|||

wow thanks for this kaushal.. il try it out :)