Sqlsever scientific notation to standard value, sqlsever count

Sqlsever scientific notation to standard value, sqlsever count

 

I. Solutions

Convert 2e-005 to 0.00002

Update table name set column name = cast (column name as float) as decimal (19,5) where column name like '% e %'

If 2e-006 is converted to 0.000002, then decimal (19,6), and so on.

Ii. test:

Select cast ('+ 1.590759e + 01' as float) as decimal (19,5 ))

Result: 15.90759

Iii. Practical application:

Hg (mercury, nvarchar type, considering that there are more than or less symbols, all nvarchar type storage is used)

Data problem example:

<0.00001
3e-005

Conversion: update WaterQualityTemp set Hg = cast (Hg as float) as decimal (19,5) where Hg like '% e %'

Result:

<0.00001
0.00003

4. Another Method

-- Test
Select convert (decimal (19,5), convert (float, '+ 1.590759e + 01 '))

-- Practical application

Update WaterQualityTemp set Hg = convert (decimal (19,5), convert (float, Hg) where Hg like '% e %'

 

---------------- Complete data operation -----------------------------------------------

-- Select all Excel operations, right-click to set the cell format, text (considering the value greater than or less than the symbol)
-- Import database in Excel, renamed Temp
-- Insert the Temp data into WaterQuality (add the ID column before the insertion, and set the primary key and auto-increment. The ID column of WaterQuality is temporarily removed from auto-growth. Be sure to change it back later)
-- Insert into WaterQuality select * from [dbo]. Temp
-- Convert scientific notation to standard value
-- Test Case
-- Select cast ('+ 1.590759e + 01' as float) as decimal (19,5 ))
-- Practical application
-- Update WaterQuality set Hg = cast (Hg as float) as decimal (19,5) where Hg like '% e %'

-- View results
-- Select * from WaterQuality

-- Another method: update WaterQualityTemp set Hg = convert (decimal (), convert (float, Hg) where Hg like '% e %'
-- Test Case
-- Select convert (decimal (19,5), convert (float, '+ 1.590759e + 01 '))
-- Practical application
-- Insert into WaterQualityTemp select * from [dbo]. Temp
-- Select * from WaterQualityTemp
-- Update WaterQualityTemp set Hg = convert (decimal (19,5), convert (float, Hg) where Hg like '% e %'

 

Reference: http://bbs.csdn.net/topics/350179663