Converting exponential number to decimal in SQL Server-CodePudding

Trying to convert the below value to proper decimal values.

column_name
3.4202999999999997E-2

I tried these steps:

TRY_CONVERT(decimal(30, 10), [column_name]),
CAST([column_name] AS float)

Any help is much appreciated

CodePudding user response：

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));

CodePudding user response：

I'm assuming your input data type is text based, so let's say NVARCHAR. If you convert is to float, you might lose precision. It really depends on your use case, but you can try with a manual processing of the exponential formula like this (more detail below):

SELECT CASE SUBSTRING(c1, CHARINDEX('E', c1) 1, 1)
        WHEN '-' THEN CAST(CAST(SUBSTRING(c1, 1, CHARINDEX('E', c1)-1) AS decimal(30,20)) / CAST(POWER(10, SUBSTRING(c1, CHARINDEX('E', c1) 2, 99)) AS INT) AS decimal(30,20)) 
        WHEN ' ' THEN CAST(CAST(SUBSTRING(c1, 1, CHARINDEX('E', c1)-1) AS decimal(30,20)) * CAST(POWER(10, SUBSTRING(c1, CHARINDEX('E', c1) 2, 99)) AS int) AS decimal(30,20)) 
    END AS [ResultValue]
FROM dbo.test

I used this as test to simulate:

DROP TABLE IF EXISTS dbo.test
CREATE TABLE dbo.test
( c1 NVARCHAR(50) )

insert into test (c1) values ('3.4202999999999997E-2')
insert into test (c1) values ('1.2342999999999997E 3')

This is the parsing in steps which parts then used in the CASE statement:

SELECT  SUBSTRING(c1, 1, CHARINDEX('E', c1)-1) AS [BaseValue],
        SUBSTRING(c1, CHARINDEX('E', c1) 1, 1) AS [ShiftDirection],
        POWER(10, SUBSTRING(c1, CHARINDEX('E', c1) 2, 99)) AS [ShiftAmount]
FROM test