SQL: How to Parse Scientific Notation to a Decimal

Excel likes to write numbers into scientific notation. This raises a problem if you are trying to message data into a database from an excel file. (In order to read the Excel, we used Spreadsheet gear.Net assembly and then converted that to XML using

In C# code it is fairly easy to parse any kind of string to a decimal using

decimal.TryParse(myString, NumberFormat.Float, out myDecimal);

However, in SQL it becomes a little trickier, mostly because any attempt to parse a number that is not a decimal raising a Cannot convert varchar to decimal. Alternatively, when the scientific notation is large (e.g. 4E+16 which is 40,000,000,000,000,000) then you might also get an arithmetic overflow operation if you try to cast that to a Decimal(18,6).

The real problem with both of these errors are that they are batch-aborting errors. (See http://www.sommarskog.se/error-handling-I.html for a good explanation). The gist of if is that you must catch these errors in a try catch block, or turn off the SQL feature that raises those kinds of warnings before you parse something that may throw these kinds of errors. Then after you parse you can check to see if the value IS NOT NULL, and you know it is a real decimal. Enough talk, let's see some sql!

--List of nasty values from excel
--NA
--Ref
--4E+04
--6E-07
--2E-05
--2
--4E+16
--45
--[blank]
--[blank]
--3.445
--Div0
--Name
--4.879667
--NULL
--NULL
--NULL

DECLARE @TempPricesFromExcel TABLE
(
StringValue VARCHAR(50), --The value from Excel
IsNumber BIT, --This is needed because when called in an Update statement or function ISNUMERIC sometimes returns true when it is not a number!
PriceAsFloat FLOAT, --An intermediate value in the process, since float is more flexible than a decimal
FinalPrice DECIMAL(18,6) --Getting the value to this last one is the tricky part.
)

--Insert all the values
INSERT @TempPricesFromExcel (StringValue) VALUES ('NA')
INSERT @TempPricesFromExcel (StringValue) VALUES ('Ref')
INSERT @TempPricesFromExcel (StringValue) VALUES ('4E+04')
INSERT @TempPricesFromExcel (StringValue) VALUES ('6E-07')
INSERT @TempPricesFromExcel (StringValue) VALUES ('2E-05')
INSERT @TempPricesFromExcel (StringValue) VALUES ('2')
INSERT @TempPricesFromExcel (StringValue) VALUES ('4E+16')
INSERT @TempPricesFromExcel (StringValue) VALUES ('45')
INSERT @TempPricesFromExcel (StringValue) VALUES ('')
INSERT @TempPricesFromExcel (StringValue) VALUES ('')
INSERT @TempPricesFromExcel (StringValue) VALUES ('3.445')
INSERT @TempPricesFromExcel (StringValue) VALUES ('Div0')
INSERT @TempPricesFromExcel (StringValue) VALUES ('Name')
INSERT @TempPricesFromExcel (StringValue) VALUES ('4.879667')
INSERT @TempPricesFromExcel (StringValue) VALUES (NULL)
INSERT @TempPricesFromExcel (StringValue) VALUES (NULL)
INSERT @TempPricesFromExcel (StringValue) VALUES (NULL)


SELECT * FROM @TempPricesFromExcel --Make sure we got the data


UPDATE @TempPricesFromExcel SET IsNumber = ISNUMERIC(StringValue) --Save these results becuase ISNUMERIC is a little finnicky

SELECT * FROM @TempPricesFromExcel --Make sure it looks right

UPDATE @TempPricesFromExcel
SET PriceAsFloat = CASE -- This is a basic parse, eliminating NULL or blank values
WHEN StringValue IS NULL THEN NULL
WHEN LEN(StringValue) = 0 THEN NULL
WHEN ISNUMERIC(StringValue) = 1 THEN CONVERT(FLOAT, StringValue) --Convert to float
ELSE 0
END
WHERE IsNumber = 1

SELECT * FROM @TempPricesFromExcel

SET ANSI_WARNINGS OFF --Turn all these warnings off so any rows that don't parse will simply be left NULL
SET ARITHABORT OFF
SET ARITHIGNORE ON

UPDATE @TempPricesFromExcel SET FinalPrice = CONVERT(DECIMAL(18,6), PriceAsFloat)

SET ARITHIGNORE OFF
SET ARITHABORT ON
SET ANSI_WARNINGS ON

SELECT * FROM @TempPricesFromExcel --Now we have our list of all the values that could parse into the decimal of our chosen size.

As this procedure goes along, it first checks to see if the values are NUMERIC, and saves that in a separate column. Parsing to a float first is necessary so we can see what values are valid as a number, but could not ultimately be parsed into a decimal of our chosen length. The problem with DECIMAL datatype is that you have to specify a length. It is just like VARCHAR. In both cases you get errors when data is going to be truncated.