Tim,

This is very close to what I need, but using floats runs into the same problem I've been having with my attempts.  Your method, like my previous attempts, would handle all but those small numbers (less than zero) in which there is only a single non-zero digit.  Numbers like .1, .01, .001, etc. should come out .10, .010, .0010, etc.  having them in float drops the zero.

Although it is hard-coded to only handle 2 significant digits, and would have to be modified to handle a different number of sig digits, below is what I came up with so far (forgive the fact that the case statement blocking gets deleted when I paste the statement in here).

Dennis

ALTER

FUNCTION [dbo].[2SignificantDigits] ( @Value decimal(18,9))

RETURNS varchar

(20) as

BEGIN

RETURN(

Select

Case When Floor(@Value) = 0 Then -- First handle all the values that are less than zero

Case

When Substring(Cast(@Value As varchar(20)),3,1) <> '0' Then

Case

When Substring(Cast(@Value As varchar(20)),4,1) = '0' Then

Cast(Cast(Cast(Round(@Value,2) As varchar(20)) + '0' As decimal(18,2)) As varchar(20))

When Substring(Cast(@Value As varchar(20)),4,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,2) As varchar(20)) As decimal(18,2)) As varchar(20))

End

Else

Case

When Substring(Cast(@Value As varchar(20)),4,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,3) As varchar(20)) As decimal(18,3)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),5,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,4) As varchar(20)) As decimal(18,4)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),6,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,5) As varchar(20)) As decimal(18,5)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),7,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,6) As varchar(20)) As decimal(18,6)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),8,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,7) As varchar(20)) As decimal(18,7)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),9,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,8) As varchar(20)) As decimal(18,8)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),10,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,9) As varchar(20)) As decimal(18,9)) As varchar(20))

Else '0.0'

End

End

End

End

End

End

End

End

Else -- Now handle those values greater than zero

Case CharIndex('.',@Value)

When 2 Then Cast(Cast(Cast(Round(@Value,1) As varchar(20)) As decimal(18,1)) As varchar(20))

When 3 Then Cast(Cast(Cast(Round(@Value,0) As varchar(20)) As decimal(18,0)) As varchar(20))

When 4 Then Cast(Cast(Cast(Round(@Value,-1) As varchar(20)) As decimal(18,0)) As varchar(20))

When 5 Then Cast(Cast(Cast(Round(@Value,-2) As varchar(20)) As decimal(18,0)) As varchar(20))

When 6 Then Cast(Cast(Cast(Round(@Value,-3) As varchar(20)) As decimal(18,0)) As varchar(20))

When 7 Then Cast(Cast(Cast(Round(@Value,-4) As varchar(20)) As decimal(18,0)) As varchar(20))

When 8 Then Cast(Cast(Cast(Round(@Value,-5) As varchar(20)) As decimal(18,0)) As varchar(20))

End

End)

END

-- Function