Cast Varchar to Float Resulting In (Scientific) E Notation

Question

Cast Varchar to Float Resulting In (Scientific) E Notation

RonMexico

Hall of Fame

Points: 3474
More actions
June 3, 2016 at 7:15 am

#400236

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 8:54 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RonMexico Hall of Fame Points: 3474 More actions · Answer 2

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?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RonMexico Hall of Fame Points: 3474 More actions · Answer 5

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

waxingsatirical SSC Eights! Points: 934 More actions · Answer 7

Luis Cazares (6/3/2016)
using float is just lazy programming.

and by 'lazy' you mean??? Not doing unnecessary work?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

waxingsatirical SSC Eights! Points: 934 More actions · Answer 11

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Joe Celko SSCertifiable Points: 5749 More actions · Answer 13

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