0

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

2
Contributors
2
Replies
3
Views
9 Years
Discussion Span
Last Post by mafaisal
0

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

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.