For reasons I can not help I have a varchar column with data like the following: 820.0E-12, 10.0E+00.
I want the numeric value. So I have this test query which works:
declare @d varchar(256) set @d = '820.0E-12' select CASE WHEN @d like '%E-%' THEN LTRIM(RTRIM(CAST(CAST(@d AS FLOAT) AS DECIMAL(18,18)))) WHEN @d like '%E+%' THEN NULL ELSE @d END
My result is: 0.000000000820000000 (which is what I want)
I change my SQL to account for the numbers > 0 (10.0E+00) like this:
WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)
My result changes to: 8.2E-10 (which is NOT what I want)
If I change @d='10.0E+00' then I get 10 (which is correct).
I've got a view that I need to make the output from a varchar column, that contains scientific notation, casted/converted into decimal(18,18).
Can somebody tell me what craziness is going on here?
Or, maybe my question should be, how do I cast/convert a varchar scientific notation column to decimal output in a view?
My first WHEN statement works for numbers < 0 but I also need to account for numbers > 0. When I change the second WHEN, to include the CAST, it breaks/gives the wrong result.
To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.
That “E” means “exponent” which is easier to understand if you aren't a math geek. Truth is, SQL Server lets you use either a D or an E in scientific notation, thus making for multiple ways to confuse you.
SQL Server's CAST() and CONVERT() methods can be used to convert VARCHAR to INT.
The cast and convert functions provide similar functionality. They are used to convert a value from one data type to another. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
There's a couple different problems all coming together here at the same time. Let's look at some of them:
You're casting numbers as DECIMAL(18, 18). What that means is "give me a number that has room for a TOTAL of 18 characters, and 18 of them should be after the decimal". That works fine as long as your number is smaller than 0 (which is true for all E- numbers) but it will break if you try to use it on numbers > 0. For numbers > 0, just cast as DECIMAL without specifying anything else.
In the case where you add "WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)", you're getting different results for numbers < 0 because the engine is implicitly casting the result differently. I don't know the rules on how sql server decides to cast CASE results, but apparently making your proposed change causes the engine to recast it in a different way. Explicitly casting those results as decimal fixes the issue.
You need to LTRIM and RTRIM your results consistently. You can either add LTRIM and RTRIM to each case statement, or you can just LTRIM and RTRIM the results of the case.
Here's a solution that should totally solve everything:
SELECT LTRIM(RTRIM(CASE WHEN @d like '%E-%' THEN CAST(CAST(@d AS FLOAT) AS DECIMAL(18,18)) WHEN @d like '%E+%' THEN CAST(CAST(@d AS FLOAT) AS DECIMAL) ELSE @d END))
you can use ISO "real" datatype
SELECT convert(numeric(18,18),convert(real,'820.0E-12')) --OR with more precision SELECT convert(numeric(18,18),convert(float(53),'820.0E-12'))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With