CODEWITHSUNDEEP
sql-serversql-server-2012type-conversionnumericvarchar
DineshDB
DineshDB

Reputation: 6193

How do I convert a varchar containing a number in scientific notation to a numeric in SQL Server?

I made some calculations using function and get the following value. I want to divide this value with 1000.

1.83673e+006

I want to convert the value into numeric.

When I try to convert it to numeric it throws

Error converting data type nvarchar to numeric.

Is it possible to convert this value to numeric? Please help me to get this.

Thanks in advance

Upvotes: 2

Views: 5081

Answers (3)

user7715598
user7715598

Reputation:

Just try this

Declare @data varchar(100)='1.83673e+006'
SELECT @data=CAST(CAST( @data AS float) AS NUMERIC)/1000
SELECT @data Result


Result
1836.730000

Upvotes: 0

Heinzi
Heinzi

Reputation: 172408

In SQL Server, scientific notation (...+e...) is only used for floating-point data types, not for decimal/numeric data types.

Thus, SQL Server recognizes this format only when converting to a floating-point data type (such as float or real), not when converting directly to numeric.

Obviously, nothing prevents you from converting the value to float first and then to numeric:

SELECT CONVERT(numeric(19,4), CONVERT(float, '1.83673e+006'))

yields 1836730.0000.

Upvotes: 2

Vecchiasignora
Vecchiasignora

Reputation: 1315

try it

SELECT CONVERT(numeric(16,0), CAST(1.83673e+006 AS FLOAT))

Upvotes: 3

Related Questions