Convert float to char

Select Cast('100.1234' as float)
give me the result 100.1234
Now when I convert it back to char I want exactly 100.1234
Select Convert(char(100),Cast('100.1234' as float))
Gives me 100.123 (Here I was expecting 100.1234)

When I do
Select STR(Cast('100.1234' as float),25,4)
I get back the result as 100.1234

However here I am not sure how many digits do I have after the decimal
point. If I put some value like
Select STR(Cast('100.1234' as float),25,8)
I get 0's appended to it, which is again not desired.

Thanks in advance,
Jai
jaijai_kumar@.hotmail.com wrote:

> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai

Hi Jai. I want to warn you about going down this path.
You will never get exactly what you want all the time,
because there is a base converson from base 10 to binary,
and there are lots of distressingly simple-seeming
decimal numbers that do not convert exactly to a binary
float. Then converting back will show the loss by giving
a different value. I suggest using a DECIMAL datatype
if you want to retain exactitude...

Joe Weinstein at BEA Systems|||(jaijai_kumar@.hotmail.com) writes:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.

Since a float is an approximate number, this is not any exact science.
Consider:

SELECT cast('100.1235' as float)

this gives in Query Analyzer:

100.12350000000001

So when you convert it to string, which value do you want?

Anyway, here is a horrible expression that achieves what you are looking
for. But note the caveate above, and be aware that you may not always
get what you want.

Select reverse(substring(x, patindex('%[^0]%', x), 25))
from (Select x = reverse(ltrim(str(Cast('100.1235' as float), 25, 8)))) y

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is another horrible expression, that does the same thing:

select replace(rtrim(replace(ltrim(str(Cast('100.1235' as float),
25, 8)),'0',' ')),' ','0')

Razvan|||Hello all,

Thanks a lot for all your messages.
Erland your work around gave me a good hint.
I have created a small function to do the same.

Jai
jaijai_kumar@.hotmail.com wrote:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai