Format 2

[hvisionexpo]

I have data stored as 1, 2, 50, 100

and I need to always display it in three number format such as 001, 002, 050, 100

How can I apply this format in the select clause to get it in this format?

---------------------------------------------

I also have another column that has values .5, .25, -.25 and I need to display them with +/- sign with 2 decimal places.
i.e. +0.50, +0.25 and -0.25.

How can I apply this format in the select clause to get it in this format?


-----------------------
THANKS

 

[gmmastros]

First one... Convert data to varchar, add 3 zero's in front of your data, and then take the right 3 characers.

Select Right('000' + Convert(VarChar(3), Field), 3)

for the second case, you'll probably need to use case to add the '+' sign. Again, convert to varchar to get the 'insignificant' zero's.

Select Left(case When Field >= 0 Then '+' End
+ Convert(VarChar(10), Field)
+ '0000'
, 5)

This is untested, but should be a decent head start for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zemp]

Thanks George, just what I was looking for.

zemp

 

[rmnastor]

very good!