Format Number in Sql Query

[mafaisal]

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

[QVeen72]

Hi,

Try this SQL Statement:

SELECT REPLICATE('0', 4 - DATALENGTH(MyCol)) + MyCol AS TempCol FROM MyTable

Note: You will get Null, if the Column Length Exceeds 4 chars..
It is always a Good Practice, to first Check the Maximum Length of the Column, and write SQL statement accordingly..

Regards
Veena

[mafaisal]

Hello Veena

Thanx For the Reply

It is Working with some modifcations

SELECT REPLICATE('0', 4 - len(Code)) + Convert(nvarchar(20),Code) AS TempCol FROM Students