How to change the format of a auto generated ID column

Question

0.00/5 (No votes)

See more:

Hi,
Can anyone tell me How to change the format of a auto generated ID column in Sql server as
if ID is 1 i want to make it 001,
2 to 002 ..so on.
but i want this in Select Query without creating any column for this format.

Any help will be appreciated. Thanks in advance Smile | :)

Thanks

Posted 27-Jun-13 19:27pm

Saroj Kumar Sahu

Add a Solution

3 solutions

Solution 1

May this helps

SQL

CREATE TABLE Increment
(ID INT IDENTITY,Val CHAR(2))
INSERT Increment VALUES('AB'),('CD'),('EF')

SELECT REPLACE(STR(Id,3),' ',0)[Id],Val FROM Increment

Id	Val
001	AB
002	CD
003	EF

Posted 27-Jun-13 19:43pm

Radhadeep12

Comments

Saroj Kumar Sahu 28-Jun-13 1:51am

thanks for reply.will it works if my ID is 10 digit ?means if My ID is 12456 then is it make any reflect ?actually if my iD is 1 or 2 digit then i want to add 0 otherwise not.

Radhadeep12 28-Jun-13 2:25am

You can use Case construct to apply condition as per your need as below:-

SELECT
CASE
WHEN LEN(Id) < 2 THEN REPLACE(STR(Id,3),' ',0)
ELSE Id
END AS Id,
Val FROM Increment

Saroj Kumar Sahu 28-Jun-13 3:03am

hi,
this one i tried but its not working.any other solution ?

Radhadeep12 28-Jun-13 6:10am

Adarsh has solved your problem.

Code given by me also works. Just need to convert it to char or varchar

SET IDENTITY_INSERT Increment ON
INSERT Increment(Id,Val) VALUES(12345,'AB')
SET IDENTITY_INSERT Increment OFF

SELECT * FROM Increment

ID Val
1 AB
2 CD
3 EF
12345 AB

SELECT
CASE
WHEN LEN(Id) < 2 THEN CONVERT(CHAR(5),REPLACE(STR(Id,3),' ',0))
ELSE CONVERT(CHAR(5),ID)
END AS Id, Val FROM Increment

Id Val
001 AB
002 CD
003 EF
12345 AB

Solution 3

Refer the links below for similar articles:
Custom Auto-Generated Sequences with SQL Server[^]
Auto generated sequence number in SQL Server[^]

--Amit

Posted 27-Jun-13 23:36pm

_Amy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Adarsh chauhan · Accepted Answer · 2013-06-27T23:32:00

Solution 2

Hi Saroj,

Try this,

SQL

CREATE TABLE test1
(ID INT IDENTITY,Val CHAR(2))

INSERT into test1 VALUES('AA'),('BB'),('CC'),('EE'),('FF'),('GG'),('HH'),('II'),('JJ'),('KK'),('LL'),('MM')

select case when LEN(ID)<=2 then right(('000'+convert(varchar,ID)),3) else convert(varchar,ID) end as ID,Val
from Test1

Result :

ID	Val
001	AA
002	BB
003	CC
004	EE
005	FF
006	GG
007	HH
008	II
009	JJ
010	KK
011	LL
012	MM

Hope it helps... :)

Posted 27-Jun-13 23:32pm

Adarsh chauhan

Updated 27-Jun-13 23:34pm

v2

Comments

Saroj Kumar Sahu 28-Jun-13 5:57am

thnks for help..it works.

Adarsh chauhan 28-Jun-13 5:58am

welcome.. :)