Microsoft SQL Server forum - Convert float to char

Author

Convert float to char

jaijai_kumar@hotmail.com

2005-08-26, 8:23 pm

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),Ca
st('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

Joe Weinstein

2005-08-26, 8:23 pm

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),Ca
st('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

Erland Sommarskog

2005-08-26, 8:23 pm

(jaijai_kumar@hotmai
l.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),Ca
st('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(Ca
st('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

Razvan Socol

2005-08-28, 3:23 am

Here is another horrible expression, that does the same thing:

select replace(rtrim(replac
e(ltrim(str(Cast('10
0.1235' as float),
25, 8)),'0',' ')),' ','0')

Razvan

jaijai_kumar@hotmail.com

2005-08-30, 8:24 pm

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),Ca
st('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

Sponsored Links