i need before 3 zeros like this query

Posted by Cnpphani under Sql Server on 3/26/2009 | Views : 1264 | Status : [Member] | Replies : 3
i need before three zeros through

ex select max(id) from emp then retrive

1
2
3
now i need 0001
002 like that how those zeros come through sqlquery




Responses

Posted by: Vuyiswamb on: 3/26/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
This is how its done

step :1

CREATE table test
(
ID int null
)


step :2
insert into test
values(1)


insert into test
values(2)


insert into test
values(3)


insert into test
values(4)


select * from test


SELECT '000' + Convert(varchar,([ID])) FROM test


Thank you for posting at Dotnetfunda
[Administrator]

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: BABU_AKKANDI on: 4/6/2009 [Member] Starter

Up
0
Down
Hi,

Use this Query,

SELECT REPLACE(STR(ID,4),' ',0) FROM emp 


Hope it Helps!

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 7/13/2009 [Member] [MVP] Silver

Up
0
Down
I think this is what u want

SELECT REPLICATE(0,3-LEN(ID)) + CAST(ID AS VARCHAR) FROM TEST

0001
0002
0004
0010
0011
0100
0101
1000
1001


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response