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.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

3 comments:

Anonymous said...

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

Siddhartha Goswami said...

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

Siddhartha Goswami said...

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