By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,359 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,359 IT Pros & Developers. It's quick & easy.

Format Number in Sql Query

mafaisal
100+
P: 142
Hello All

I am Using sql2005
My Doubt is How To Format Numbers in Sql Query Like
Format(NOS,"0000") in vb

ie I have Table Tbl1 Fields Nos1 Datatype int values 1,2,3,4,................1000

When Simple Query Select * From Tbl1 We got Following o/p
1,2,3,4

But I want Out Put Like below
0001,0002,0003 ,------------, 0010,0011,---------,0100,0101,--------,1000

How we Can Format like this in sql Query

Faisal
Jun 17 '08 #1
Share this Question
Share on Google+
4 Replies


100+
P: 142
There is no specific funtionality to do it. You can use Convert method to do some date and string formats. Try to know how to use Convert method in Sql Server.
Jun 18 '08 #2

mafaisal
100+
P: 142
Hello balame

I know convert method (Convert(Nvarchar(20),Nos) Like
But hw we can get specified format above
Any way thanx For Reply

Faisal

There is no specific funtionality to do it. You can use Convert method to do some date and string formats. Try to know how to use Convert method in Sql Server.
Jun 18 '08 #3

mafaisal
100+
P: 142
Hello

I Got Solution

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT REPLICATE('0', 4 - len(Code)) + Convert(nvarchar(20),Code)  AS TempCol FROM Students
  3.  
  4.  
Faisal
Jun 18 '08 #4

ck9663
Expert 2.5K+
P: 2,878
Hello

I Got Solution

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT REPLICATE('0', 4 - len(Code)) + Convert(nvarchar(20),Code)  AS TempCol FROM Students
  3.  
  4.  
Faisal
Or simply...

Expand|Select|Wrap|Line Numbers
  1. SELECT right(REPLICATE('0',4) + cast(code as varchar(4)),4) as TempCol FROM Students
  2.  
-- CK
Jun 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.