I See Sharp I See Sharp - 5 months ago 10
SQL Question

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

****UPDATED****

How to convert an exponent and coefficient to an integer? Is there a built-in method in SQL?

This is the value in scientific notation

6,1057747657e+011

Answer
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.)