Auto Generate AlphaNumeric ID’s in a SQL Server Table

I was recently working on a query to auto generate a Column ID based on an IDENTITY column. I used computed columns to achieve this. If you too have a similar requirement, then here’s the query.

DECLARE @TT TABLE (
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)

INSERT INTO @TT VALUES ('Dhiraj')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Dhiraj')

SELECT * FROM @TT

OUTPUT

Auto Generate SQL Server Column

Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.

at

3 comments:

  1. AnonymousMarch 22, 2013 at 10:04 AM

    but it will be placing the c-0010
    after c-001

    it looks like this
    c-001
    c-0010
    c-0011
    ....
    c-002
    c-0020
    c-0021

    ReplyDelete
  2. UnknownJuly 25, 2016 at 11:03 PM

    TRY THIS QUERY
    CREATE TABLE EMPLOYEE
    (
    SrNo INT IDENTITY(1,1) NOT NULL,
    EmpCode AS 'EMP' + RIGHT ('0000' + CONVERT(VARCHAR(5),SrNo),5) PERSISTED,
    EmpName VARCHAR(100),
    EmpSalary DECIMAL(10,2)
    )
    ----
    INSERT INTO EMPLOYEE(EmpName,EmpSalary) VALUES('SIDDHARTHA',10000)
    ----
    EmpCode
    EMP00001
    .
    .
    EMP00009
    THEN
    EMP00010

    ReplyDelete
  3. UnknownJuly 25, 2016 at 11:05 PM

    TRY THIS QUERY
    CREATE TABLE EMPLOYEE
    (
    SrNo INT IDENTITY(1,1) NOT NULL,
    EmpCode AS 'EMP' + RIGHT ('0000' + CONVERT(VARCHAR(5),SrNo),5) PERSISTED,
    EmpName VARCHAR(100),
    EmpSalary DECIMAL(10,2)
    )
    ----
    INSERT INTO EMPLOYEE(EmpName,EmpSalary) VALUES('SIDDHARTHA',10000)
    ----
    EmpCode
    EMP00001
    .
    .
    EMP00009
    THEN
    EMP00010

    ReplyDelete