June 3, 2016 at 7:15 am
I have a pipe delimited text file which I bulk insert into a SQL table with all fields being of data type varchar. The problem I'm having is that when a value has four or more consecutive zeros after the decimal, casting from a varchar to a float results in scientific notation. For example select cast('0.00004' as float) results in 4E-05 whereas select cast('0.00044' as float) results in 0.00044 and select cast('0.0004' as float) results in 0.0004. How can I avoid this while still casting as a float?
June 3, 2016 at 7:21 am
Scientific notation is just the display of the value. The value is still 0.00004 or something very similar as float is an approximate numeric data type.
June 3, 2016 at 7:26 am
I probably phrased my original question incorrectly. Is there a way to display the value as 0.00004 instead of 4E-05 while still casting it as a float?
June 3, 2016 at 7:34 am
RonMexico (6/3/2016)
I probably phrased my original question incorrectly. Is there a way to display the value as 0.00004 instead of 4E-05 while still casting it as a float?
No, that's the display for float. If you use decimal or numeric, you'll get the value without the scientific notation.
What's the reason for a specific display? What's the reason to use float instead of an exact numeric?
June 3, 2016 at 7:37 am
floats are a pain. As Luis said, the underlying value is still digits, but when being presented in the GUI or exported out again, it's a bit ugly.
when converting a float to a varchar, there's an optional style you can add, but you end up getting scientific notation, but with better detail.
the new style3 is for 2016 and azure only, so on a 2012 server, it defaults to the blank style
that doesn't happen with decimal datatypes.
/*--results
val styleblank style0 style1 style2 style3
4E-05 4e-005 4e-005 4.0000000e-005 4.000000000000000e-005 4e-005
0.00044 0.00044 0.00044 4.4000000e-004 4.400000000000000e-004 0.00044
0.0004 0.0004 0.0004 4.0000000e-004 4.000000000000000e-004 0.0004
*/
WITH MyCTE(val)
AS
(
select cast('0.00004' as float) UNION ALL
select cast('0.00044' as float) UNION ALL
select cast('0.0004' as float)
)
SELECT val,
convert(varchar(30),val) as styleblank,
convert(varchar(30),val,0) as style0,
convert(varchar(30),val,1) as style1,
convert(varchar(30),val,2) as style2,
convert(varchar(30),val,3) as style3 --azure /SQL2016 only 17 digit lossless
FROM MyCTE
Lowell
June 3, 2016 at 7:52 am
Luis/Lowell,
Thanks for all of the information. The web developers requested the float data type. It sounds like I'll need to talk to them about the shortcomings of the way it displays.
June 3, 2016 at 8:04 am
The display can be changed in the front-end by the web developers. They might be using float in the front end, but it doesn't mean that the values need to be stored in float as well. In my opinion, using float is just lazy programming.
August 25, 2016 at 3:37 am
Luis Cazares (6/3/2016)
using float is just lazy programming.
and by 'lazy' you mean??? Not doing unnecessary work?
August 25, 2016 at 7:00 am
waxingsatirical (8/25/2016)
Luis Cazares (6/3/2016)
using float is just lazy programming.and by 'lazy' you mean??? Not doing unnecessary work?
If by unnecessary work you mean analysis and planning, yes it's lazy programming. Some programmers will assign a float to any numeric value. Correct typing is a way (out of many) to differentiate the good ones.
August 25, 2016 at 7:47 am
Wryan138 (6/3/2016)
Ron,If they really must have their floating point columns you might want to try:
Str(ThatFloatField, 5, 2) -- nnn.nn
It will round the result (not truncate).
Wayne
That would slow things down even more. STR is notoriously slow compared to other methods. It would be much better to convince the "developers" that FLOAT is the wrong thing to use unless the scale of the number varies in an astronomical way.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2016 at 7:49 am
waxingsatirical (8/25/2016)
Luis Cazares (6/3/2016)
using float is just lazy programming.and by 'lazy' you mean??? Not doing unnecessary work?
No. More like not wanting to be bothered with best practice details, not taking the time to understand the "Why", or not doing necessary work at the beginning to prevent a lot of unnecessary work later.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2016 at 9:12 am
Well what is the "Why"? Why are floats not considered best practice by some?
I don't know what unnecessary work later is incurred by using floats, but I know of lots that is incurred by using decimals. Such as, resizing columns when data sizes go up or accuracy changes, having to constantly avoid multiplication gotchas which cause tricky-to-spot rounding bugs.
August 25, 2016 at 9:41 am
waxingsatirical (8/25/2016)
Well what is the "Why"? Why are floats not considered best practice by some?I don't know what unnecessary work later is incurred by using floats, but I know of lots that is incurred by using decimals. Such as, resizing columns when data sizes go up or accuracy changes, having to constantly avoid multiplication gotchas which cause tricky-to-spot rounding bugs.
The main cause is that float is an approximate numeric data type. You can get some unpleasant surprises when trying to do math or retrieving a different value that what you expected to have stored.
Here's an example: http://www.sqlservercentral.com/Forums/Topic1811927-2799-1.aspx
August 25, 2016 at 10:24 am
I probably phrased my original question incorrectly. Is there a way to display the value as 0.00004 instead of 4E-05 while still casting it as a float?
You have missed the fundamental principle of tiered architecture. The database layer gets a result, and passes it to a presentation layer. The presentation layer is where the sort of stuff is done. Things coming out of the database layer have standardized display formats; dates in SQL use ISO 8601 (yyyy-mm-dd HH:MM:ss.sss), and floating-point numbers use the ANSI/ISO standard E notation. You probably notice that decimal point use periods – you are probably too young to remember when Europeans were fighting for the use of commas instead when Algol 60 was proposed.:w00t:
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 25, 2016 at 11:19 pm
waxingsatirical (8/25/2016)
Well what is the "Why"? Why are floats not considered best practice by some?I don't know what unnecessary work later is incurred by using floats, but I know of lots that is incurred by using decimals. Such as, resizing columns when data sizes go up or accuracy changes, having to constantly avoid multiplication gotchas which cause tricky-to-spot rounding bugs.
FLOAT definitely has its useful place in the world but shouldn't be used generically nor even for most things that require decimal math because of the very definition of the FLOAT datatype in SQL Server. From the following link...
https://msdn.microsoft.com/en-us/library/ms173773.aspx
... we get the following definition of what the FLOAT datatype is. The emphasis is mine...
[font="Arial Black"]Approximate-number [/font]data types for use with floating point numeric data. [font="Arial Black"]Floating point data is approximate[/font]; therefore, [font="Arial Black"]not all values in the data type range can be represented exactly[/font].
A great example came up in a post just two days ago. It does the simple calculation of...
A B
--- - ---
C C
Assigning some simple values, as below, figure out the answer in your head or using your favorite 4 function calculator and you find that it works out to be ...
A B 7 2
--- - --- = --- - --- = .7 - .2 = .5
C C 10 10
Now, let's put that to code using the FLOAT datatype. Looking at the code below and knowing that the correct answer to the problem is .5 and so the result of the code should be "GE.5", explain why it isn't.
--===== Create variables and assign values
DECLARE @A FLOAT = 7
,@B FLOAT = 2
,@C FLOAT = 10
;
--===== Solve the problem as stated in the previously mentioned formula
-- and use CASE to determine if the answer is >= .5 or less than .5.
SELECT CASE
WHEN @A/@C - @B/@C >= 0.5 THEN 'GE.5'
ELSE 'LT.5'
END
;
What's really frustrating is that the following code "proves" that it should all work out just fine.
--===== Create variables and assign values
DECLARE @A FLOAT = 7
,@B FLOAT = 2
,@C FLOAT = 10
;
SELECT @A/@C, @B/@C, @A/@C - @B/@C, CAST(.5 AS FLOAT)
;
Results:
---------------------- ---------------------- ---------------------- ----------------------
0.7 0.2 0.5 0.5
(1 row(s) affected)
Microsoft did a pretty good job of masking the "problem" by "correcting" the display but that doesn't correct the actual results. You can see the "problem" that occurs during the subtraction using the following code, which does the FLOAT math and then displays the results as DECIMAL so that you can actually see why the CASE example failed to provide the correct answer. (I have "problem" in quotes because its not a problem if you understand BINARY floating point math and, ironically, how to round to get the actual correct answer as a true value instead of just a displayed value).
--===== Create variables and assign values
DECLARE @A FLOAT = 7
,@B FLOAT = 2
,@C FLOAT = 10
;
SELECT CAST(@A/@C AS DECIMAL(38,28))
,CAST(@B/@C AS DECIMAL(38,28))
,CAST(@A/@C - @B/@C AS DECIMAL(38,28))
;
Results:
--------------------------------------- --------------------------------------- ---------------------------------------
0.7000000000000000000000000000 0.2000000000000000000000000000 0.4999999999999999400000000000
(1 row(s) affected)
You see, FLOAT in SQL Server is NOT actually DECIMAL math behind the scenes. It's BINARY math and, with the limited precision of "only" 15 digits, not all DECIMAL numbers can successful be converted to BINARY with 100% accuracy. Hence the term "imprecise" in most peoples definition of FLOAT and "approximate" in the MS definition. Here's some more "fun". WYSI[font="Arial Black"]N[/font]WYG 😉 The column on the left is what is displayed and the column on the right is the number actually used.
--===== Create variables and assign values
DECLARE @A FLOAT = 7
,@B FLOAT = 2
,@C FLOAT = 10
;
SELECT LooksLike = CAST(number AS FLOAT)/10
,ActuallyIs = CAST(CAST(number AS FLOAT)/10 AS DECIMAL(38,28))
FROM master.dbo.spt_values
WHERE type = 'P'
AND number BETWEEN 0 AND 20
;
Results:
LooksLike ActuallyIs
---------------------- ---------------------------------------
0 0.0000000000000000000000000000
0.1 0.1000000000000000000000000000
0.2 0.2000000000000000000000000000
0.3 0.3000000000000000000000000000
0.4 0.4000000000000000000000000000
0.5 0.5000000000000000000000000000
0.6 0.6000000000000000000000000000
0.7 0.7000000000000000000000000000
0.8 0.8000000000000000000000000000
0.9 0.9000000000000000000000000000
1 1.0000000000000000000000000000
1.1 1.1000000000000001000000000000
1.2 1.2000000000000000000000000000
1.3 1.3000000000000001000000000000
1.4 1.3999999999999999000000000000
1.5 1.5000000000000000000000000000
1.6 1.6000000000000001000000000000
1.7 1.7000000000000000000000000000
1.8 1.8000000000000000000000000000
1.9 1.8999999999999999000000000000
2 2.0000000000000000000000000000
(21 row(s) affected)
And you say FLOAT avoids the "tricky" problem of rounding? 😀 Check out what MS says about FLOAT and rounding at the following URL...
https://msdn.microsoft.com/en-us/library/ms190476.aspx
... where it clearly states that FLOAT has a rounding "problem" (emphasis is mine).
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
[font="Arial Black"]Approximate numeric data types do not store the exact values specified for many numbers[/font]; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. [font="Arial Black"]Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, [/font]in operations involving rounding, [font="Arial Black"]or in equality checks. Instead, use the integer, decimal,
money, or smallmoneydata types.[/font]Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.
The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. [font="Arial Black"]Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.[/font]
Sounds like a "Best Practice" to me.
Also remember that FLOAT has a fixed precision of 15 digits with a floating decimal point. As the number of digits on the left side of the decimal points goes up, you lose scale... which is another kind of (sort of) "rounding" problem... the very problem that you're trying to avoid.
This is also why Luis said "it's lazy" of developers to use FLOAT for everything (or most anything) and I agreed. If they had taken the time to look all that stuff up, they wouldn't be so adamant about using FLOAT to make things "easy". Using the correct datatypes for the precision and scale expected is not a form of pre-optimization... and it's not just a "Best Practice". It's absolutely necessary for coming up with the right bloody answer. 😉
P.S. You should also avoid the use of any of the "MONEY" datatypes and small scale DECIMAL types for any work with any operators other than simple addition and subtraction.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply