CODEWITHSUNDEEP
sql-serverformat
user3157778
user3157778

Reputation: 108

Convert float to nvarchar keeping decimal variable part

I have a problem converting float to string but t-sql returns it with scientific format.

Example:

declare @amount float
set @amount=5223421341.23
print cast(@amount as nchar(20))

returns "5.22342e+009"

Well I tried the function STR but there is the point: I don't know how many decimals could have the float and I don't want to round it.

There is a way to return the float as nchar with the same precision as float is declared?

Thanks a lot.

Upvotes: 2

Views: 2370

Answers (3)

Mike Nakis
Mike Nakis

Reputation: 61969

According to Microsoft SQL Server documentation, (https://msdn.microsoft.com/en-us/library/ms173773.aspx) float is a synonym for float(53), and a float(53) has a precision of 15 digits.

But the 15 digits of precision say nothing about the exponent. The exponent is about 300. Which means that floats are capable of representing numbers containing about 300 zeros in them. That's why scientific notation is indispensable.

If scientific notation was not a problem for you, 21 characters would be enough to hold 15 digits with a period among them, plus the trailing e+999.

But since scientific notation is an issue for you, then you have to have a text field large enough to hold numbers of the form 0.0000000000000000000000000000000...00000000000000000000000000723237435435644 (that's almost 300 zeros followed by 15 digits) and 377733453453453000000000000000000...00000000000000000000000000000000000000.0 (that's 15 digits followed by almost 300 zeros.)

So clearly, you are going to need some pretty huge text fields for what you are trying to do.

So, bottom line is, instead of looking for a solution to the problem as stated, I would recommend revising what you are trying to accomplish here.

Upvotes: 0

Joe C
Joe C

Reputation: 3993

There is a problem with the decimals places. The below code shows that the decimal value is distorted and I cant find a way around it. If there was a way to determine the precision of the float then the result could be rounded to a correct value.

With an unknown precision, I have yet to find a solution.

Declare @Amount float
Set @Amount=5223421341.23

Select LTrim(RTrim(Str(@Amount, 1000, 1000)))

Produces : 5223421341.2299995000000000

Based on what I am reading floats are only approximations by definition so this may be the best you can get.

https://msdn.microsoft.com/en-us/library/ms173773.aspx

http://www.webopedia.com/TERM/F/floating_point_number.html

Note that most floating-point numbers a computer can represent are just approximations. One of the challenges in programming with floating-point values is ensuring that the approximations lead to reasonable results. If the programmeris not careful, small discrepancies in the approximations can snowball to the point where the final results become meaningless.

Upvotes: 1

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

According to Microsoft, the best method in SQL is likely to use the STR function.

declare @amount float
set @amount=5223421341.23
print str(@amount,20,6)
print convert(VARCHAR(50),rtrim(ltrim(str(@amount,20,6))))

This seems like it would cover most scenarios, but you need to find out the max and min values in your data set and also the max precision.

Upvotes: 1

Related Questions