MASK: Extracting or Adding Characters

How to:

The MASK function extracts characters from or adds characters to an alphanumeric string. It can extract a substring from different parts of the parent string, and can insert characters from a parent string into another substring. For example, it can extract the first two characters and the last two characters of a string to form a single substring.

MASK works by comparing the characters in a mask to the characters in a source field. When it encounters a 9 in the mask, MASK copies the corresponding character from the source field to the new field. When it encounters a dollar sign in the mask, MASK ignores the corresponding character in the source field. When it encounters any other character in the mask, MASK copies that character to the corresponding position in the new field.

MASK replaces the masking functionality of the EDIT function that is available in the reporting language.


Top of page

x
Syntax: How to Extract or Add Characters
MASK(fieldname, 'mask')

where:

fieldname

Is the source field.

mask

Is a character string enclosed in single quotation marks, or a temporary field that contains the string.



Example: Extracting a Character From a Field

MASK extracts the first initial from the FIRST_NAME field:

MASK(FIRST_NAME, '9$$$$$$$$$')

The following are sample values for FIRST_NAME and the values for the result of the MASK function:

FIRST_NAME    MASK_FIRST_NAME 
----------    ---------------
MARY          M
DIANE         D
JOHN          J
ROSEMARIE     R
MARY          M
BARBARA       B


Example: Adding Dashes to a Field

MASK adds dashes to the EMP_ID field:

MASK(EMP_ID, '999-99-9999')

The following are sample values for EMP_ID and the values for the result of the MASK function:

EMP_ID       MASK_EMP_ID
------       -----------
112847612    112-84-7612
117593129    117-59-3129
219984371    219-98-4371
326179357    326-17-9357
543729165    543-72-9165
818692173    818-69-2173

Information Builders