Convert Varchar to Decimal with scientific notation

dataservices job design
1

Hi,

I have a VARCHAR column that contains numbers that I need to convert to a DECIMAL(38,12)

Most of the numbers are regular decimals eg 334.98, 878.11332, etc

However, some of the numbers are in the column in scientific notation eg 2.8873322531580925E-2

The TO_DECIMAL_EXT function works perfectly for the normally formatted numbers, but errors on the numbers that contains scientific notation.

Any ideas as to how to get around this as right now I am stumped.

bungy :us: (BOB member since 2007-10-04)

2

Hi

it would be good if the varchar column avoids using scientific notation but I guess this is out of your control? If at all possible I would look to fix this ‘root cause’ i.e. if it came from a csv from Excel then change number format in Excel.

One option to look at would be to push the conversion down to the database for example oracle’s to_number.

However I guess you can also apply a workaround by manually converting these numbers. Given that 3E-02 just means 3 x 10 to power of -2 = 0.03 then we can use,

if the value contains an E then take the numbers to the left of the E and multiply this by 10 to the power of the numbers to the right of the E.

So for your example 2.8873322531580925E-2 you would calculate 2.8873322531580925 * (10 to the power of -2)

use index function to locate position of E and then substring to get the two numeric values. probably want to put this together in a custom function.

AL

agulland :uk: (BOB member since 2004-03-17)

3

Thanks for the response.

I have come up with this handy function if any-one else needs it:

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

bungy :us: (BOB member since 2007-10-04)

4

Here’s the inner workings of a custom function that handles +ve and -ve exponents:

$V_FORMATTED_NUMBER = $P_INPUT_STRING ;

Process positive exponents 1

if (lower($P_INPUT_STRING) like ‘%e+%’)
begin
$V_POSITION = index(lower($P_INPUT_STRING) ,‘e’, 1);
$V_VALUE = substr($P_INPUT_STRING ,1, $V_POSITION -1);
$V_EXPONENT = ltrim( substr( $P_INPUT_STRING , $V_POSITION +2, 99),‘0’);
$V_FORMATTED_NUMBER = $V_VALUE * power( 10, $V_EXPONENT );
end

Process positive exponents 1

if (lower($P_INPUT_STRING) like ‘%e%’ and lower($P_INPUT_STRING) not like ‘%e+%’ and lower($P_INPUT_STRING) not like ‘%e-%’)
begin
$V_POSITION = index(lower($P_INPUT_STRING) ,‘e’, 1);
$V_VALUE = substr($P_INPUT_STRING ,1, $V_POSITION -1);
$V_EXPONENT = ltrim( substr( $P_INPUT_STRING , $V_POSITION +1, 99),‘0’);
$V_FORMATTED_NUMBER = $V_VALUE * power( 10, $V_EXPONENT );
end

Process negative exponents

if (lower($P_INPUT_STRING) like ‘%e-%’)
begin
$V_POSITION = index(lower($P_INPUT_STRING) ,‘e’, 1);
$V_VALUE = substr($P_INPUT_STRING ,1, $V_POSITION -1);
$V_EXPONENT = ltrim( substr( $P_INPUT_STRING , $V_POSITION +2, 99),‘0’);
$V_FORMATTED_NUMBER = $V_VALUE * power( 10, $V_EXPONENT*-1 );
end

if (is_valid_double( $V_FORMATTED_NUMBER , ‘##########.##########’) = 0)
$V_FORMATTED_NUMBER = ‘’;

return $V_FORMATTED_NUMBER ;

Barry_Carlino (BOB member since 2010-12-16)