By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,386 Members | 1,676 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,386 IT Pros & Developers. It's quick & easy.

Convert float to char

P: n/a
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

Aug 26 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a


ja**********@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

Aug 26 '05 #2

P: n/a
(ja**********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 26 '05 #3

P: n/a
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

Aug 28 '05 #4

P: n/a
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
ja**********@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


Aug 30 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.