Numeric information is considered to be monetary if the FORMAT phrase contains a currency symbol. Use the characters in the following table in the FORMAT phrase to control formatting of monetary numeric information.
| Character | Meaning |
|---|---|
| G | Invokes the currency grouping rule defined by CurrencyGroupingRule in the SDF. The value of CurrencyGroupSeparator in the current SDF is copied to the output string to separate groups of digits to the left of the currency radix separator, according to the currency grouping rule defined by CurrencyGroupingRule. Grouping applies to the integer portion of REAL, FLOAT, or DOUBLE PRECISION data. The G must appear as the first character in a FORMAT phrase. A currency character, such as L or C, must also appear in the FORMAT phrase. If the FORMAT phrase does not contain the letter G, no grouping is done on the output string. The G cannot appear in a FORMAT phrase that contains any of the following characters:
|
| / : % |
Insertion characters. Copied to output string where they appear in the FORMAT phrase. The % insertion character cannot appear in a FORMAT phrase that contains S, and cannot appear between digits in a FORMAT phrase that contains G, D, or E. For example, GL9999D99% is valid, but L9(9)D99%E999 is not. The / and : insertion characters cannot appear in a FORMAT phrase that contains any of the following characters:
|
| B | Insertion character. A blank is copied to the output string wherever a B appears in the FORMAT phrase. B cannot appear between digits in a FORMAT phrase that contains G, D, or E. For example, GNB99D99 is valid, but G9(9)BD99 is not. |
| + - |
Sign characters. These characters can appear at the beginning or end of a format string, but cannot appear between Z or 9 characters, or between repeated currency characters. One sign character places the edit character in a fixed position for the output string. If two or more of these characters are present, the sign floats (moves to the position just to the left of the number as determined by the stated structure). Repeated sign characters must appear to the left of formatting characters consisting of a combination of the radix and any 9 formatting characters. If a group of repeated sign characters appears in a FORMAT phrase with a group of repeated Z characters or a group of repeated currency characters or both, the groups must be contiguous. For example, +++$$$ZZZ. One trailing sign character can occur to the right of any digits, and can combine with B and one currency character or currency sign. For example, G9(I)B+L. The trailing sign character for a mantissa cannot appear to the right of the exponent. For example, 999D999E+999+ is invalid. The + translates to + or - as appropriate; the - translates to - or blank. |
| $ £ ¥ ¤ ¤ |
Currency signs:
A currency sign cannot appear between Z or 9 formatting characters, or between repeated sign characters. One currency sign places the edit character in a fixed position for the output string.If a result is formatted using a single currency sign with Zs for zero-suppressed decimal digits (for example, £ZZ9.99), blanks can occur between the currency sign and the leftmost nonzero digit of the number. If the same currency sign appears more than once, the sign floats to the right, leaving no blanks between it and the leftmost digit. A currency sign cannot appear in the same phrase with a currency character, such as L. If + or - is present, the currency character cannot precede it. If a group of repeated currency signs appears in a FORMAT phrase with a group of repeated sign characters or a group of repeated Z characters or both, the groups must be contiguous. For example, +++$$$ZZZ. One currency sign can occur to the right of any digits, and can combine with B and one trailing sign character. For example, G9(I)B+$. A currency sign cannot appear to the right of an exponent. For example, 999D999E+999B+$ is invalid. |
| L C N O U A |
Currency characters. The value of the corresponding currency string in the current SDF is copied to the output string whenever the character appears in the FORMAT phrase.
A currency character cannot appear between Z or 9 formatting characters, or between repeated sign characters. If the same currency character appears more than once, the value that is copied to the output string floats to the right, leaving no blanks between it and the leftmost digit. Repeated characters must be contiguous, and must appear to the left formatting characters consisting of a combination of the radix and any 9 formatting characters. If a group of repeated currency characters appears in a FORMAT phrase with a group of repeated sign characters or a group of repeated Z characters or both, the groups must be contiguous. For example, +++LLLZZZ. A currency character cannot appear in the same phrase with any of the following characters:
One currency character can occur to the right of any digits, and can combine with B and one trailing sign character. For example, G9(I)B+L. A currency character cannot appear to the right of an exponent. For example, 999D999E+999B+L is invalid. |
| V | Implied decimal point position. Internally, the V is recognized as a decimal point to align the numeric value properly for calculation. Because the decimal point is implied, it does not occupy any space in storage and is not included in the output. V cannot appear in a FORMAT phrase that contains the 'D' radix symbol or the '.' radix character. |
| Z | Zero-suppressed decimal digit. Translates to blank if the digit is zero and preceding digits are also zero. A Z cannot follow a 9. Repeated Z characters must appear to the left of any combination of the radix and any 9 formatting characters. The characters to the right of the radix cannot be a combination of 9 and Z characters; they must be all 9s or all Zs. If they are all Zs, then the characters to the left of the radix must also be all Zs. If a group of repeated Z characters appears in a FORMAT phrase with a group of repeated sign characters, the group of Z characters must immediately follow the group of sign characters. For example, --ZZZ. |
| 9 | Decimal digit (no zero suppress). |
| E | For exponential notation. Defines the end of the mantissa and the start of the exponent. The exponent consists of one optional + or - sign character followed by one or more 9 formatting characters. |
| CHAR(n) |
For more than one occurrence of a character, where CHAR can be one of the following:
and n can be:
If n is F, CHAR can only be Z or 9. If n is an integer constant, the (n) notation means that the character repeats n number of times. For the meanings of I and F, see the definitions later in this table. |
| , | Currency grouping character. The comma is inserted only if a digit has already appeared. The comma is interpreted as the currency grouping character regardless of the value of the CurrencyGroupSeparator in the SDF. The comma cannot appear in a FORMAT phrase that contains any of the following characters:
|
| . | Currency radix character. The period is interpreted as the currency radix character, regardless of the value of the CurrencyRadixSeparator in the SDF, and is copied to the output string. The period cannot appear in a FORMAT phrase that contains any of the following characters:
|
| D | Radix symbol. The value of CurrencyRadixSeparator in the current SDF is copied to the output string whenever a D appears in the FORMAT phrase. A currency symbol, such as a dollar sign or yen sign, must also appear in the FORMAT phrase. The D cannot appear in a FORMAT phrase that contains any of the following characters:
|
| I | The number of characters needed to display the integer portion of numeric and integer data. I can only appear as n in the CHAR(n) character sequence (see the definition of CHAR(n) earlier in this table), where CHAR can be:
CHAR(I) can only appear once, and is valid for the following types:
The value of I is resolved during the formatting of the monetary numeric data. The value is obtained from the declaration of the data type. For example, I is eight for the DECIMAL(10,2) type. If CHAR(F) also appears in the FORMAT phrase, CHAR(F) must appear to the right of CHAR(I), and one of the following characters must appear between CHAR(I) and CHAR(F):
|
| F | The number of characters needed to display the fractional portion of numeric data. F can only appear as n in the CHAR(n) character sequence (see the definition of CHAR(n) earlier in this table), where CHAR can be:
CHAR(F) is valid for the DECIMAL/NUMERIC data type. The value of F is resolved during the formatting of the monetary numeric data. The value is obtained from the declaration of the data type. For example, F is two for the DECIMAL(10,2) type. A value of zero for F displays no fractional precision for the data; however, the value of CurrencyRadixSeparator in the current SDF is copied to the output string if a D appears in the FORMAT phrase.
CHAR(F) can appear only once. If CHAR(I) also appears in the FORMAT phrase, CHAR(F) must appear to the right of CHAR(I), and one of the following characters must appear between CHAR(I) and CHAR(F):
|
| - | Dash character. Used when storing numbers such as telephone numbers, social security numbers, and account numbers. A dash appears after the first digit and before the last digit, and is treated as an embedded dash rather than a sign character. A dash cannot follow any of these characters:
|
| S | Signed Zoned Decimal character. Defines signed zoned decimal input as a numeric data type and displays numeric output as signed zone decimal character strings. When converting signed zone decimal input to a numeric data type, the final character is converted as follows:
When displaying numeric output as signed zone decimal character strings, the final character indicates the sign, as follows: If the final data digit is 0, then the final result digit is displayed as:
If the final data digit is 1, then the final result digit is displayed as:
If the final data digit is 2, then the final result digit is displayed as:
If the final data digit is 3, then the final result digit is displayed as:
If the final data digit is 4, then the final result digit is displayed as:
If the final data digit is 5, then the final result digit is displayed as:
If the final data digit is 6, then the final result digit is displayed as:
If the final data digit is 7, then the final result digit is displayed as:
If the final data digit is 8, then the final result digit is displayed as:
If the final data digit is 9, then the final result digit is displayed as:
The S must follow the last decimal digit in the FORMAT phrase. It cannot appear in the same phrase with the following characters.
For examples and details on signed zone decimal conversion, see “Signed Zone Decimal Conversion” in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. |