Numeric information is considered to be non-monetary if the FORMAT phrase does not contain any currency or dual currency symbols. Use the following characters in the FORMAT phrase to control formatting of non-monetary numeric information.
| Character | Meaning |
|---|---|
| G | Invokes the grouping rule defined by GroupingRule in the SDF. The value of GroupSeparator in the SDF is copied to the output string to separate groups of digits to the left of the radix separator, according to the grouping rule defined by GroupingRule. 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. 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:
|
| D | Radix symbol. The value of RadixSeparator in the current SDF is copied to the output string whenever a D appears in the FORMAT phrase. The D 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, G9999D99% is valid, but 9(9)D99%E999 is not. The / and : insertion characters cannot appear in a FORMAT phrase that contains any of the following characters:
|
| , | Grouping character. The comma is inserted only if a digit has already appeared. The comma is interpreted as the grouping character regardless of the value of GroupSeparator in the SDF. The comma cannot appear in a FORMAT phrase that contains any of the following characters:
|
| . | Radix character. The period is interpreted as the radix character, regardless of the value of RadixSeparator 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:
|
| 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, 999B99B9999 is valid, but G9(9)BD99 is not. |
| + - |
Sign characters. Can appear at the beginning or end of a format string, but cannot appear between Z or 9 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 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, the group of Z characters must immediately follow the group of sign characters. For example, --ZZZ. One trailing sign character can occur to the right of any digits, and can combine with B. For example, G9(I)B+. The trailing sign character for a mantissa cannot appear to the right of the exponent. For example, 999D999E+999+ is not valid. The + translates to + or - as appropriate; the - translates to - or blank. |
| 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 FORMAT phrase containing Z characters (including Z(I) and Z(F)), a combination of commas, dots, G, or D, and no other formatting characters means “blank when zero.” For example, ZZZZZ, ZZ.Z, GZ(I)DZ(F), GZZZZZZDZZ and Z,ZZZ.ZZ print only blanks if the number is 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). |
| CHAR(n) | For more than one occurrence of a character, where CHAR can be:
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. |
| 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 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 RadixSeparator 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 displaying 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:
|
| U+6642 時 U+5206 分 U+79D2 秒 |
Kanji JI, FUN, and BYOU time markers. Each marker can appear only once. For more information on using Kanji time markers, see FORMAT Phrase, DateTime Formats, and Japanese Character Sets. |
| 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 according to the rules 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. |
| 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. |
| FN9 | Specifies a variable-length format that can be up to 64 characters as follows: 1. If the value is negative, a - sign character is generated, otherwise a space is generated. 2.The integral portion of the value is generated (without leading zeroes or blanks). 3. If a fractional portion is present, the value of RadixSeparator in the SDF is generated followed by the minimum number of digits required to express the fractional portion. 4. If the resulting string is longer than 64 characters, the value is formatted using the FNE format. This format is defined later in this table. |
| FNE | Specifies a variable-length exponential format as follows:
|