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

Question

****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 1

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