446,386 Members | 1,676 Online
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
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.