CODEWITHSUNDEEP
sqlsql-server
Mahajan344
Mahajan344

Reputation: 2550

Convert scientific notation back to numbers in SQL Server

I had data in Excel like

7540006
7540447

But when I import the data into SQL Server, it is saved as 

7.54001e+006
7.54045e+006

So now when I try to convert it to back to original state, it's not ending up with the correct value.

I have tried following queries for conversion

declare @a varchar(40)
set @a = '7.54001e+006'

declare @b decimal(27, 12)

SELECT @b = CONVERT(REAL, @a, 2)

SELECT LTRIM(RTRIM(str(@a))), LTRIM(STR(@a))

SELECT CAST('7.54001e+006' as REAL)

and the output I am getting is addition of 3 to original value for all methods i.e.

7540010
7540050

How do I convert it back to original state ??

Upvotes: 19

Views: 72170

Answers (5)

Edutecni
Edutecni

Reputation: 1

If your database use comma you most replace comma to dote ex:

SELECT CAST(REPLACE('1,018E+12', ',', '.') AS FLOAT)

Upvotes: 0

Daz
Daz

Reputation: 41

I use:

ISNULL(TRY_CONVERT(numeric(19,5),'9.8E-4'), CAST(CAST('9.8E-4' AS REAL) AS numeric(19,5)))

This ensures that only values that can't convert naturally are cast as REAL

Upvotes: 0

developer
developer

Reputation: 1

when you will save in database that time convert value into toString() like

Convert.toString(7540006)

then it will save original value in database.

Upvotes: 0

sandeep rawat
sandeep rawat

Reputation: 4957

All data is stored as Unicode string 255 (DT_WSTR) in excel.

Read excel data as Unicode form. then do conversion in ssis or database using.

SELECT CAST('7.54001e+006' as REAL)

In excel data source >> connection manager >>Data access mode == QUERY

     SELECT * FROM [SheetName$]

Upvotes: 4

Nithila
Nithila

Reputation: 301

Try the following query which gives the exact value

select CAST(CAST('7.54001e+006'  AS FLOAT) AS bigint)

Upvotes: 30

Related Questions