Numeric conversion routines - Connect_CDC - connect_cdc_mimix_share - Latest

The following numeric conversion routines require that the character input be compatible with the types to which they are being converted:

• tonumber(character) returning integer

• todouble(character) returning double

• todecimal(character) returning decimal

These numeric conversions require the following:

• The digits in the character input strings must be numeric with some exceptions. That is, the strings cannot contain any leading, trailing, or embedded spaces or special characters or alphabetics.

The exceptions are:

• Double and decimal strings can contain a decimal point to indicate fractional numerics and a plus or minus sign to indicate positive or negative. For example:

todecimal('100.00');
todecimal('-100.00');
todecimal('+100.00');

• Double strings can contain the standard "E" notational characters. For example:

todouble('1.55E02'); yields 155.0
todouble('1.55E-02'); yields 0.0155
todouble('-.55E02'); yields -55.0
todouble('+.55E02'); yields 55.0

• To trim leading spaces before converting to a numeric, one could code:

todecimal(trim('  100','L'));

See Trim for more information.

• A dollar sign in a decimal string (for example, '$') is not acceptable; this will result in a number format exception. To remove this, one could code the following:

todecimal(trim('$100','L','$'));

• Decimal points are not acceptable in numeric strings that you want to convert to integer using tonumber. For instance, the following FAILS with a number format exception:

tonumber('11.42'); <=== WILL FAIL!!

To make this work, one could code either of the following:

• Convert to a decimal and then round:

round(todecimal('11.42')); yields the number 11

• Extract the numeric portion to the left of the decimal point and then convert to an integer.

tonumber(substring('11.42',1,2)); yields the number 11

For example:

tonumber(trim('        '));   <=== WILL FAIL!!

This trims all the spaces and results in a zero-length string. This results in a NumberFormat exception because the trimmed value (a zero-length) string is not a numeric character and cannot be converted.

You could code this as follows:

begin returns integer;
/* get rid of leading and trailing spaces */
declare char trimmed = trim(<column>,'B');
/* check for zero-length string */
if (length(trimmed) == 0)
return 0;
else
return tonumber(trimmed);
end;