Format Masks

               

Expand/Collapse all Show/Hide All

A mask can be used to format the conversion of numeric data to string data or to format string data. A format mask consists of a character string that describes how the number or character is converted. One output character is generated for each character present in the format mask. Format masks allow for the generation of fixed format data with the insertion of fill characters (usually spaces) to suppress leading/trailing zeroes.

The following types of format masks are used:

 

Numeric Format Masks

The following table describes the valid characters that can be used for numeric masks.

Expand/Collapse item  Numeric Masks Table

Mask

Format Result

0 (Zero)

Outputs one digit from the numeric value or zero if the digit is to the left of the first significant digit.

#

Outputs one digit from the numeric value.

.

Outputs and aligns the decimal point. Only one is allowed per mask.

!

Treated as a '.' but causes the output to be replaced with spaces if the value is zero (blank when zero).

-

In front or at the end of a mask, inserts '-' if the value is negative or a fill character if the value is positive. If in front, outputs just before the first digit. If within the mask, outputs a dash, regardless of the value.

+

Outputs either a '-' if negative or '+' if a zero or positive value. If in front of a mask, outputs just before the first digit.

,

Outputs a comma if some digits have been output; otherwise, outputs a fill character.

$

If located in front of the number, indicates that a dollar sign is to be output in front of the first digit of the number. When located anywhere else, outputs the dollar sign at that position.

*

If located before any digits of the number, causes an asterisk to be used as the fill character instead of a space. If located anywhere else, causes an asterisk to be output.

(or)

Outputs parentheses if the value is negative; otherwise, outputs a fill character.

CR

Outputs CR if the value is negative; otherwise, outputs two fill characters.

DR

Outputs CR if the value is negative; otherwise, outputs DR.

B

Outputs a space.

Other

Outputs the character.

 

Expand or collapse item Numeric Masks Sample

Value

Mask

Result

1

"000000"

"000001"

1

"####0"

"    1"

-2.4

"-###0.00"

" -2.40"

1000.9

"#,##0+"

"1,001+"

-10.5

"$#,##0.00BDR"

" $10.50 CR"

5551212

"000-0000"

"555-1212"

2349

"00:00"

"23:49"

-45

"-###0"

"-45"

When applying masks to numeric values, you must use the STR function around the entire expression, as shown in the following example.

If ML_BALANCE$ contains "1234.56" then:

STR (NUM(ML_BALANCE$):"###,###.00-") yields " 1,234.56"

 

String Format Masks

String masks can be used to validate the content of a string variable, as well as format the output. If the data does not validate, a null value is returned.

The following table describes the valid characters that can be used for string masks.

Expand or collapse item String Format Masks Table

Mask

Format Result

0 (Zero)

String must contain a digit in this position (0-9).

A

String must contain a character value in this position (A-Z or a-z). The output of the format mask is converte d to uppercase.

a

String must contain a character value in this position (A-Z or a-z).

X

String can contain any character in this position. The output of the format mask is converted to uppercase.

x

String can contain any character in this position.

Z

String must contain an alphabetic letter (A-Z or a-z) or a digit (0-9) in this position. The output of the for mat mask is converted to uppercase.

z

String must contain an alphabetic letter (A-Z or a-z) or a digit (0-9) in this position. (nn)

(nn)

A numeric value surrounded by parentheses can be used to specify a repeat count for the preceding format character. For example, AAAAA can also be specified as A(5).

Other

Outputs the character.

Like numeric masks, the expression using the string mask must use an STR function to return a string value.

Expand or collapse item Example

If ML_NAME$ contains "Abcdef" then:

STR (ML_NAME$:"XXX-XXX") yields "ABC-DEF"

STR (ML_NAME$:"xx-xx-xx") yields "Ab-cd-ef"

© Sage Group plc 2019. Privacy Policy | Copyright/Trademarks