Caché SQL Reference
A MultiValue formatting function for a string.
||A MultiValue string expression to be formatted for display.
||A quoted string consisting of positional letter and number codes specifying the display format for string.
function returns the string
value formatted as specified by format
. This formatting may include padding or rounding/truncating of string
. The most common use for $MVFMT
is to provide a uniform display format for decimal numbers.
string has the following format:
||Optional The overall width of the display field, specified as a positive integer. Used to impose a uniform width (number of characters) on string. Different operations are performed if w is larger or smaller than the length of string, as described below.
||Optional A fill character, specified as a single character. (Certain fill characters, as described below, must be specified as a quoted string.) You must specify w to use f. If you specify w, but do not specify f, it defaults to the space character.
||Optional The letter 'R' or 'L' specifying right or left justification. This letter code is not case sensitive. If you do not specify a letter code, $MVFMT defaults to left justification. (The letters 'T' and 'U' are synonyms for 'L').
||Optional The number of fractional digits to the right of the decimal place, specified as a positive integer. If you specify n, it must either be the only code in format, or it must be preceded by the letter 'R' or 'L'. If you do not specify n, $MVFMT defaults to number of fractional digits in string.
There are two basic uses of format
To return fractional numbers in a standard form.
To return strings in a standard form.
For fractional numbers, the most basic format
, where R is either the letter 'R' specifying right justification or the letter 'L' specifying left justification, and n
is the number of digits to the right of the decimal point to display. If string
is an integer or has fewer fractional digits than n
, zero padding is added. If string
has more digits than n
, the number is rounded to the specified number of fractional digits. If n
is zero, the number is rounded to an integer and the decimal point is removed. If string
is less than 1, specifying n
supplies a zero (0) to the left of the decimal point. If string
contains any character other than a number, the decimal point character, or a minus sign, $MVFMT
does no zero padding or rounding.
For strings, the most basic format
, where w is an integer specifying width and f is a literal fill character (for example '9^'
). You can use w
(width) and f
(fill) formatting to make a display field a standard width. By default, the justification is 'L' (left); you can, of course, specify 'R' for right justification.
A more complex example of format
, where 10 is the overall width of the display field, # is the fill character to use to fill out the display field. Because 'R' indicates right justification, these fill characters will appear to the left of the string
value. The 5 indicates that the string
value is to have 5 digits to the right of the decimal place.
(width) value may be larger than, equal to, or smaller than the number of characters (including the decimal point) of string
. If string
is a fractional number, w
is applied after $MVFMT
adjusts the number of fractional digits (by rounding or zero padding).
is greater than the length of string
fill characters to string
to make the resulting string w
characters in length. If 'L' (left justification) fill characters are applied to the end of the string; if 'R' (right justification) fill characters are applied to the beginning of string.
is equal to the length of string
(after rounding or zero padding of fractional digits), no operation is performed.
is less than the length of string
inserts a Text Mark (@TM, CHAR(251)) character after every w
count of characters. If L (left justification), characters are counted forward from the beginning of the string; if R (right justification), characters are counted backward from the end of the string. $MVFMT
then appends f
fill characters so that all Text Mark delimited substrings are w
characters long (the Text Mark itself is not counted). If 'L' (left justification) fill characters are applied to the end of the string; if 'R' (right justification) fill characters are applied to the beginning of string.
The fill character is optional; if omitted, filling is done with blank spaces. The fill character cannot be the same as the format string delimiter character
. If the fill character is a number, the backslash (\), or the letters L, R, T, or U, it must be enclosed in string delimiter quotes that are different than the format
string. For example: '10"0"R2'
. You cannot use the backslash as a string delimiter for the fill character.
The following example uses 'Rn' formatting to format numeric values so that they display four decimal digits. Note that both zero padding and rounding are performed as needed:
SELECT $MVFMT(1.2,'R4'), /* Returns 1.2000 */
$MVFMT(1.77777,'R4'), /* Returns 1.7778 */
$MVFMT(.4,'R4'), /* Returns 0.4000 */
$MVFMT(0,'R4') /* Returns 0.0000 */