Standardizing numbers

[smain]

What is the best way to standardize a varchar(5) variable using the following rules - remove all trailing zeros to the right of the decimal point, remove any trailing decimal points, and remove all leading zeroes?

Examples
5.00 = 5
0.20 = .2

 

[tlbroadbent]

It is fairly simple to eliminate trailing zeros and decimal points.

Select cast(cast(<value> as float) as varchar(5)) As VarOut

When <value>=5.00 this statement returns 5. However, when the whole number part of <value> = 0 (i.e., 0.20) this statement returns a leading zero. Eliminating the leading zero is slightly harder.

Select
Case
When left(cast(cast(@x as float) as varchar(5)),1)='0'
Then stuff(cast(cast(@x as float) as varchar(5)),1,1,'')
Else cast(cast(@x as float) as varchar(5))
End As VarOut

Perhaps, someone can create a more elegant solution. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[smain]

Thank you. Your suggestion solved my problem.
My initial syntax was &quot;select cast(@x as float),&quot; but when a value of 0.2 was passed, the result was 0.20000000000000001.

 

[tlbroadbent]

Float is an approximate data type so imprecise numbers often result from conversion. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.