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,"000 0") 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

[balame2004]

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.

[mafaisal]

Hello balame

I know convert method (Convert(Nvarch ar(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.

[mafaisal]

Hello

I Got Solution

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

Faisal

[ck9663]

Hello

I Got Solution

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

Faisal

Or simply...

SELECT right(REPLICATE('0',4) + cast(code as varchar(4)),4) as TempCol FROM Students

-- CK