Convert Exponential to Number in SQL

Convert exponential to number in sql

Try to cast your string to float before converting it :

SELECT CONVERT(numeric(16,0), CAST(TOKEN AS FLOAT))
FROM MULTICURRENCY_CHECK

• See this fiddle.

I don't know what's the format of those numbers in your XML source, but with the data you provide, you'll end up with 33733 for instance followed by a bunch of zeroes. If you have a bigger precision in your XML, maybe you should tweak your importing settings to keep this precision instead of trying to deal with that in the DB.

EDIT:

Try testing your strings with ISNUMERIC to avoid the casting errors you're getting. Adding a raw output of your column will allow you to check which value fails to convert (i.e. converts to 0).

SELECT TOKEN, 
       CONVERT(NUMERIC(16, 0), CAST(CASE 
                                      WHEN ISNUMERIC(TOKEN) = 1
                                      THEN TOKEN 
                                      ELSE 0 
                                    END AS FLOAT)) 
FROM   MULTICURRENCY_CHECK

Converting exponential number to decimal in SQL Server

If the value is a float then this will automatically be able to be explicitly converted to a decimal:

DECLARE @f float = 3.4202999999999997E-2;
SELECT CONVERT(decimal(30,10),@f);

If it is a string based data type, you really need to address that; it is not a suitable data type for a numerical value. The value '3.4202999999999997E-2' is not a valid decimal value, so you will need to first convert it to a float, and then a decimal:

DECLARE @v varchar(50) = '3.4202999999999997E-2';
SELECT CONVERT(decimal(30,10),CONVERT(float,@v));

If you get errors from that, then you have values that are not valid float values; this is one reason why storing numerical data is so foolish. You can discard the bad data using TRY_CONVERT:

DECLARE @v varchar(50) = '3.4202999999999997ee-2';
SELECT CONVERT(decimal(30,10),TRY_CONVERT(float,@v));

Query to convert exponential number to float in SQL Server

Provide correct output for this so that we test and throw few more sample data.

Try this,

SELECT CONVERT(decimal(18,8), CAST('5E-05' AS FLOAT))

How to convert exponent and coefficient to an integer value in SQL

DECLARE @s VARCHAR(25);
DECLARE @i BIGINT;

SET @s = '6.1057747657e+011';
SET @i = CAST(@s as FLOAT(53));

SELECT @i;

Results 610577476570

You need to store the result as a BIGINT because the number is too large for a 32-bit INT. Note that an implicit conversion is being done from FLOAT(53) to BIGINT.

If you want to control the rounding, you can use the ROUND(), FLOOR() or CEILING() functions. For example:

SET @i = ROUND(CAST(@s as FLOAT(53)), -2);

If it is possible that the input string might contain an invalid number, you would need to add error handling.

DECLARE @s VARCHAR(25);
DECLARE @i BIGINT;

SET @s = 'rubbish';

BEGIN TRY
    SET @i = CAST(@s as FLOAT(53));
    SELECT @i;
END TRY
BEGIN CATCH
    -- error handling goes here
END CATCH

(Tested using T-SQL on SQL Server 2012.)

SQL Server Convert scientific notation (exponential notation) string to numeric

From what I can tell, it is simply a matter of the Float datatype being able to convert a string representing scientific notation to a number, whereas the Numeric datatype cannot. Once Float has converted a number out of scientific notation into a number (as opposed to a string that represents a number), then Numeric can handle the conversion.

In the documentation for the Float and Numeric datatypes, there is reference in both to the use of scientific notation, but this is to define the minimum and maximum values accepted by the datatype. In the Float documentation, it is stated that:

Conversion of float values that use scientific notation to decimal or
numeric is restricted to values of precision 17 digits only. Any value 
with precision higher than 17 rounds to zero.

This indicates that Float can interpret scientific notation whereas Decimal and Numeric datatypes can only handle a number with a maximum precision of 17 resulting from a number specified using scientific notation.

I hope that makes sense. Leave a comment if you have any questions.

Convert Excel Exponential Format back to its text in SQL Server 2008 R2

FORMAT doesn't exist in SQL Server 2008; but it's use is best avoided any way; it's an awfully slow function.

You can use CONVERT and the style 0 though:

SELECT REPLACE(CONVERT(varchar(10),CAST(2.00E+09 AS float),0),'+','');

This won't, however, give exactly the same format, and would return '2e009'. Based on the fact that you use the value '0E0' for the FORMAT function though (which would return '2E9' for your example value), I assume this is permissible.

How to get exponential values into given float data type values

You seem to want to get values representation as a string includes an "E". If so, your code should actually work based on the default representation, although I prefer an explicit cast:

SELECT [value] AS FloatValue
FROM #InvalidData
WHERE CAST(Value AS VARCHAR(255)) LIKE '%E+%';

This doesn't work, because you are not using the default representation.

Instead, just use a numeric comparison:

where value >= 100000000000

---

Related Topics