Implicit character to numeric conversion produces a valid result only if the character string represents a numeric value.
If a CHAR or VARCHAR character string is present in an expression that requires a numeric operand, it is read as a formatted numeric and is converted to a FLOAT value, using the default format for FLOAT.
To override the implicit format, use a FORMAT phrase.
Or, to change the default format for FLOAT, you can change the setting of the REAL element in the specification for data formatting (SDF) file. For information on default data type formats, the SDF file, and the FORMAT phrase, see Data Type Formats and Format Phrases.
To use a CLOB type in an expression that requires a numeric operand, you must first explicitly convert the CLOB to CHAR or VARCHAR.
An empty character string (zero length) or a character string consisting only of pad characters is interpreted as having a numeric value of zero.
If the default format for FLOAT is -9.99E-99, then:
| THIS expression … | IS converted to … | AND the result is … |
|---|---|---|
| 1.1*’$20.00’ | 1.10E 00*2.00E1 | 2.20E 01 |
| ’2’+’2’ | 2.00E 00+2.00E 00 | 4.00E 00 |
| ’A’ + 2 | ---------- | error |
If a column or parameter of numeric data type is specified with a string value, the string is again assumed to be a formatted numeric. For example, the following INSERT statement specifies the Salary as a numeric string:
INSERT INTO Employee (EmpNo, Name, Salary) VALUES (10022, 'Clements D', '$38,000.00');
The conversion to numeric type removes editing symbols. When selected, the salary data contains only the special characters allowed by the FORMAT phrase for Salary in the CREATE TABLE statement. If the FORMAT phrase is ‘G-(9)D9(2)’, then the output looks like this:
Salary --------- 38,000.00
If the FORMAT phrase is ‘G-L(9)D9(2)’, then the output looks like this:
Salary ---------- $38,000.00