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-01,
2 to 002-01,
3 to 003-01 ...so on.
but i want this in Select Query without creating any column for this format.
and 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.
Any help will be appreciated. Thanks in advance Smile |
Posted Jun 28, 2013, 6:08 AM
Thanks for ur reply..this one also partially correct.and here is the correct one.
select case when LEN(ID)<=2 then right(('000'+convert(varchar,ID)),3)+( '-01') else convert(varchar,ID)+( '-01') end as ID
from Table
Sanjeeb LenkaPosted Jun 28, 2013, 3:30 AM
SELECT (RIGHT('00'+ID,3)+ '-01') as ID form yourtablename
Posted Jun 28, 2013, 3:20 AM
Thanks for reply..i tried this but not working..
Sanjeeb LenkaPosted Jun 28, 2013, 3:06 AM
SELECT RIGHT('000000000'+ID,10) as ID from yourtablename
or
SELECT RIGHT(replicate('0',9)+ID ,10) as ID from yourtablename
ID: your column name
yourtablename: your table name
mark it as answered if it helps you