Scientific Notation

So the other day is was busy processing a text file which had a column full of numbers. Everything was going fine until I the numbers changed from this:

234.8909
3.998
4456.88929

to this:

34e-9980
2e253
27e-224

Data Services immediately objected that these weren't proper numbers and refused to continue. So what to do?

Well I decided to write a function that can convert varchars to decimals whether they are in regular or scientific notation. Here it is:

if (index($VarcharNumber,'E',1) IS NULL)
    begin
        Return to_decimal_ext($VarcharNumber,'.',',',38,12);
    end
else
    begin
        Return to_decimal_ext(word_ext($VarcharNumber,1,'E'),'.',',',38,24) * power(10, to_decimal_ext(word_ext($VarcharNumber,2,'E'),'.',',',38,12));
    end

Here's the breakdown of what this function does:

• if (index($VarcharNumber,'E',1) IS NULL) - First of all, work out whether this number is in regular or scientific notation. If it a regular number then there is not much to do but convert it from a varchar to a decimal.
• to_decimal_ext($VarcharNumber,'.',',',38,12); - This bit converts the varchar to a decimal using the to_decimal_ext function. The last two parameters are the scale and precision of the decimal. You might want to change these in your version of the function, or perhaps turn these into parameters to make the function a bit more flexible.
• to_decimal_ext(word_ext($VarcharNumber,1,'E'),'.',',',38,24) * power(10, to_decimal_ext(word_ext($VarcharNumber,2,'E'),'.',',',38,12)); - This is the code that converts the scientific notation varchar into a decimal. I'm going to break this down into its component pieces.

So first of all we need to get the number prior to the e. We do this using the word_ext function. This function is really helpful when you have a string come through that is divided up by set characters. In our case the letter e fulfills just that purpose. So  word_ext($VarcharNumber,1,'E') will return the first part of the varchar in the string divided by the letter e.

We then want to multiply the number we have just got by 10 to the power of the number after the letter e. word_ext($VarcharNumber,2,'E') will give us everything after the letter e.

Now that we have the number before the e and the numbers after the e, we can go ahead and work out what our number should look like in standard notation.

Lets say I had the number 23e12 and I wanted to convert it to standard notation. Here is what would happen:

• to_decimal_ext(word_ext('23e12',1,'E'),'.',',',38,24)  = 23
• power(10, to_decimal_ext(word_ext('23e12',2,'E'),'.',',',38,12)) = 10 ^ 12 = 1000000000000
• 23 * 1000000000000 = 23000000000000

This works just fine for really tiny numbers like 23e-12 as well:

• to_decimal_ext(word_ext('23e-12',1,'E'),'.',',',38,24)  = 23
• power(10, to_decimal_ext(word_ext('23e-12',2,'E'),'.',',',38,12)) = 10 ^ (-12) = 0.0000000000001
• 23 * 0.0000000000001 = 0.00000000000023