SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
9-183
Considerations
Considerations

The length of the numeric format-value must not exceed 128 characters.

The format-value causes the numeric-expression to be truncated (not
rounded) to the specified number of significant digits, for example:
TO_CHAR(14.426, ‘99.99’) produces the truncated output value, 14.42.

The elements D, FM, G, L, MI, PR, S, V, and U are case-insensitive.

If the value has more significant digits to the left of the decimal point than in the
specified format-value , the output is hash (#) sign, for example:
TO_CHAR(1254, ‘999’) produces an output ####.

All negative values have a leading negative sign (-) except when format-value
contains MI, S, or PR elements, for example:

TO_CHAR(-1234, ‘9999’) produces an output of ‘-1234’. Notice the leading
negative sign and the output does not contain MI, S, or PR

TO_CHAR(-1234, ‘9999S’) produces an output of ‘1234-’. Notice the negative
sign at the end of the output for element S

TO_CHAR(-1234, ‘9999MI’) produces an output of ‘1234-’. Notice the negative
sign at the end of the output for element MI

The elements MI, S, C, and L can appear either at the beginning or end of the
format-value, for example:

TO_CHAR(12, ‘C99’) has a valid format-value

TO_CHAR(12,‘9C9’) has an invalid format-value as the element C is
neither at the beginning nor at the end of the format-value
U Returns Euro or other dual
currency symbol.
Restriction: You can specify this
symbol only at the end of the
format-value. The $
(dollar) is the only supported
currency symbol.
12 9999U ‘ $12’
V Multiplies the specified
numeric-expression by
10^n, where n specifies the
number of 9s after V.
12 99V999 ‘ 12000’
FM Controls blank padding. 12 FM99 ‘12’
Element Description
numeric-
expressi
on
format-
value
Output