Auto gen record id as alphanumeric values

[ak1dnar]

I need a query to generate the client id automatically with in my client table.
Currently i can generate it in this format 10001,10002,10003... . but I need
another char value before the numeric value..
ex: C10001, C10002
Is there any way to do it.

Current code:

create table clients
(
c_id int not null primary key IDENTITY (10001,1),
c_name char(50) not null,
c_address char(80) not null,
c_email char (25)not null,
c_phone char(50) not null,
c_con_person char(25) not null,
c_con_person_phone char(15),
)
 

[almaz]

Do you want to change c_id column from int to varchar??? It is a very bad practice to use non-integer types as a primary key, so I suggest you to live it as is.
Please describe why do you want to use "C" prefix?

[ak1dnar]

If changing client id field from int to char is a bad idea..let me know this thing.
I'll put the same PK with the table but i want to give another unique ID for the client like C101,C102,C103...

Can i generate this using IDENTITY or is there any other way to do this..

[almaz]

It depends on whether user is permitted to change this unique ID, or it is a read-only field. If read-only - you can either add "C" to existing client_id identity column in client application, or add a calculated field

create table #a(id int identity (1,1))
insert #a default values
insert #a default values
insert #a default values
insert #a default values
insert #a default values
insert #a default values
insert #a default values
insert #a default values
insert #a default values
select * from #a
 
alter table #a add userid as 'C' + cast(id as varchar(10))
select * from #a

If user is permitted to change the value, then you can just initialize the value using identity column on row inserts:

alter table #a add userid2 varchar(10)
insert #a default values
update #a set userid2 = 'C' + cast(id as varchar(10)) where id = scope_identity()
select * from #a

[ak1dnar]

Thank u so much.
Permission is not a problem i can make it.
Please take a look at for this also.

create table #b 
(
id int identity (1001,1),
userid as 'C' + cast(id as varchar(10))
)
 
insert #b default values
insert #b default values
insert #b default values
insert #b default values
 
select * from #b

with out going for two columns like in the Query..can't we make the auto gen id in a single column with prefix Chars.

------------
ID
------------
C1
C2
C3
------------

[almaz]

No, identity columns are integer columns, so you cannot automatically fill in the alphanumeric field. You can also create a script that will search for maximum value and insert next number, but I still don't understand why do you want to get rid of integer ID column...

[ak1dnar]

Thanks for the reply again.
In my application i have to Automatically generate the Client ID. and the format of the ID should start with a Char value.

How ever i wanted to check whether there is possibility to make it on DB level. so as i feel now i have to make it from the Application level. by using the int value from the DB. anyway thanks again.

[dfrancestx]

I have a similar dilemma...

I have a unique numeric field that identifies each record, but the client wants a different 9 character unique alphanumeric field to capture each record. Any suggestions on where to start?

[almaz]

Why does client asks for 9 alphanumeric characters? To show it to user (an possibly allow to edit)? If yes - create non-visible integer identity column as primary key, and another one (9 chars, unique constraint) to be shown

[dfrancestx]

The client's database does not allow have a column to hold the 16 character identifier we have. Their database has a 9-character (alphanumeric) column, so they do not want to receive our 16-char number. Instead they want the 9-char identifier. This new field cannot be editted. In a way, it's going to act as a second primary key.

Why does client asks for 9 alphanumeric characters? To show it to user (an possibly allow to edit)? If yes - create non-visible integer identity column as primary key, and another one (9 chars, unique constraint) to be shown