DBFORMAT

Syntax

front:thousands:decimal:back

1. front is the leading currency symbol, can be an asterisk ( * ).
2. thousands is a character that you specify as a valid thousands separator, can be an asterisk ( * ).
3. decimal is a character that you specify as a valid decimal separator.
4. back is the trailing currency symbol, can be an asterisk ( * ).

Usage

The DBFORMAT environment variable defines the input and display format for numeric values.

DBFORMAT takes precedence over DBMONEY.

DBFORMAT can be set to define the input and display format for values of these types:

• MONEY (thousands separator, decimal separator and currency symbol)
• DECIMAL (thousands separator, decimal separator)
• SMALLFLOAT (thousands separator, decimal separator)
• FLOAT (thousands separator, decimal separator)
• SMALLINT (thousands separator)
• INTEGER (thousands separator)
• BIGINT (thousands separator)

DBFORMAT can specify the leading and trailing currency symbols (but not their default positions within a monetary value) and the decimal and thousands separators. The decimal and thousands separators defined by DBFORMAT apply to both monetary and other numeric data.

The instructions affected by the setting in DBFORMAT include (but are not restricted to) these items:

• USING operator.
• FORMAT field attribute.
• DISPLAY or PRINT statement (default formatting of numeric values).
• LET statement, where a CHAR, VARCHAR or STRING variable is assigned a monetary or number value.
• LOAD and UNLOAD statements that use ASCII files (or whatever the locale regards as a flat file) to pass data to or from the database.

The asterisk ( * ) specifies that a symbol or separator is not applicable; it is the default for any front, thousands, or back term that you do not define.

If you specify more than one character for decimal or thousands, the values in the decimal or thousands list cannot be separated by spaces (nor by any other symbols). However, only the first character will be used to display numeric or currency values, when converting strings to numbers and when entering values in form fields.

Any printable character that your locale supports is valid for the thousands separator or for the decimal separator, except:

• Digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
• <, >, |, ?, !, =, [, ]

The same character cannot be both the thousands and decimal separator. A blank space (ASCII 32) can be the thousands separator (and is conventionally used for this purpose in some locales). The asterisk ( * ) symbol is valid as the decimal separator, but is not valid as the thousands separator.

The colon ( : ) symbol is supported as thousands or decimal separator but must be preceded by a backslash ( \ ) symbol, as in the specification :\::.:DM.

You must include all three colons. Enclosing the DBFORMAT specification in a pair of single quotation marks is recommended to prevent the shell from attempting to interpret (or execute) any of the DBFORMAT characters.

The setting in DBFORMAT affects how formatting masks of the FORMAT attribute and USING operator are interpreted. In formatting masks of FORMAT and USING, these symbols are not literal characters but are placeholders for what DBFORMAT specifies:

• The dollar ($) sign is a placeholder for the front currency symbol.
• The comma (,) is a placeholder for the thousands separator.
• The period (.) is a placeholder for the decimal separator.
• The at (@) sign is a placeholder for the back currency symbol.

This table illustrates the results of different combinations of DBFORMAT setting and format string on the same value.

1234.56

$#,###.##

$:,:.:

$1,234.56

1234.56

$#,###.##

:.:,:DM

1.234,56

1234.56

#,###.##@

$:,:.:

1,234.56

1234.56

#,###.##@

:.:,:DM

1.234,56DM

When the user enters numeric or currency values in fields, the runtime system behaves as follows:

• If a symbol is entered that was defined as a decimal separator in DBFORMAT, it is interpreted as the decimal separator.
• For MONEY fields, it disregards any front (leading) or back (trailing) currency symbol and any thousands separators that the user enters.
• For DECIMAL fields, the user must enter values without currency symbols.

When the runtime system displays or prints values:

• The DBFORMAT-defined leading or trailing currency symbol is displayed for MONEY values.
• If a leading or trailing currency symbol is specified by the FORMAT attribute for non-MONEY data types, the symbol is displayed.
• The thousands separator is not displayed unless it is included in a formatting mask of the FORMAT attribute or of the USING operator.

When MONEY values are converted to character strings by the LET statement, both automatic data type conversion and explicit conversion with a USING clause insert the DBFORMAT-defined separators and currency symbol into the converted strings.

*:.:,:SFr

1234,56SFr

Here SFr stands for the Swiss Franc currency symbol. Values input by the user into a screen form are expected to contain commas, not periods, as their decimal separator because DBFORMAT has *:.:,:SFr as its setting in this example.

When using a graphical front-end, the decimal separator of the numeric keypad will produce the character defined by this environment variable.