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
DECLARE @s VARCHAR(25); DECLARE @i BIGINT; SET @s = '6.1057747657e+011'; SET @i = CAST(@s as FLOAT(53)); SELECT @i;
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.)