CODEWITHSUNDEEP
sqlsql-server-2012
coolstoner
coolstoner

Reputation: 799

how to create auto increment column with characters and number like alphanumeric in sql server?

i have a table with primary key column which is auto-increment id of numerals starting from 1000..

now i have been asked to make a unique key for each records which starts with C20161, C20162, C20163 and so on the series goes on.....

so it is actually an alphanumeric key that auto-increments....

i would like to know how to implement the same in sql and also can i update the same to existing records?

is 'sequence' what i am looking for or something else?

Upvotes: 3

Views: 3681

Answers (2)

Denny Sutedja
Denny Sutedja

Reputation: 538

i hope this is what you ask

first: i think you cant edit primary key.

second: if you want to update all existing data from int to varchar then first you need to change your database.

after that try to use execute

example to update from 1001 to C1001

@key nvarhcar(5)
execute('update table set key = ''C' + @var1 +''' where key = @key')

now all you need is get the key
use for to get all key.

hope this help you

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You need COMPUTED COLUMN for this purpose,

CREATE TABLE TableName(
     ID INT IDENTITY(1,1) NOT NULL,
     NewColumnName AS 'C' + CAST(ID AS VARCHAR(30)),
     .... Other columns list here
)

Upvotes: 1

Related Questions