MAP( ) function

Returns a logical value indicating if a character string matches a specified format string containing wildcard characters, literal characters, or both.

Syntax

MAP(string, format)

Parameters

string

Character. The field, expression, or literal value to test for matches.

format

Character. The data pattern, or character string, you want to compare with the string parameter. The format parameter can contain wildcard characters (shown in Table 1), literal characters, or a combination of the two. The format parameter must be enclosed in quotation marks.

Table 1. Wildcard characters

Wildcard character

Description

X or x

Matches any alphabetic character (a-z, A-Z, European characters)

This wildcard character is not case-sensitive. You can use X or x.

9

Matches any number (0-9)

!

Matches any non-blank character

?

Matches any character, including blanks

\

An escape character that specifies that the character immediately following is a literal. Use the escape character if you want to literally match any of the wildcard characters (X, x, 9, !, ?).

Output

Logical. Returns T (true) if a match is found, and F (false) otherwise.

Remarks

You can use the MAP( ) function to compare a character string against a wildcard string that contains no literal characters. Wildcard strings are useful for searching for specific data formats or data patterns while ignoring the literal content of values. For example, MAP(Invoice_Number, "XX99999") isolates all values in the “Invoice Number” field that start with two letters followed by five numbers, regardless of what the letters and numbers are.

You can also use the MAP( ) function to search for literal characters, or a mix of literal characters and wildcard characters. For example, MAP(Invoice_Number, "AB12345") isolates all values in the “Invoice Number” field that are exactly “AB12345”, or that start with “AB12345”. MAP(Invoice_Number, "AB99999") isolates all values in the “Invoice Number” field that start with “AB” followed by five numbers.

The MAP( ) function is case-sensitive when comparing two literal characters. For example, “a” is not equivalent to “A”. If the string parameter includes character data with inconsistent case, you can use the UPPER( ) or LOWER( ) functions to convert the values to consistent case before using MAP( ).

If the format parameter is shorter than the string parameter, the MAP( ) function compares characters up to the length of the format parameter, and returns True if the two parameters match up to that point. Additional characters in the string parameter are ignored. In other words, partial matching is supported. This behavior allows you to use the MAP( ) function to search for patterns among leading characters, or for specific leading characters, without the overall length of string parameter values affecting the results.

If the string parameter is shorter than the format parameter, the result is always False. In other words, partial matching is not supported. This behavior allows you to use the MAP( ) function to specify minimum lengths for values that return True in the string parameter.

In the format parameter, use a backslash (“\”) as an escape character before any of the wildcard characters (X, x, 9, !, ?) that you want to match literally in the string parameter. For example, if the string parameter must contain the letter ‘X’ in a specific position, specify \X in the corresponding position in the format parameter. Do not count the backslash when determining position. To specify a literal backslash, enter \\.

Blanks are treated as characters, and can be accounted for in either of two ways. You can match blanks literally, by including blanks in the format parameter at the appropriate position, or you can use the wildcard “?”, which matches any character, including blanks. If required, you can use the TRIM( ) or ALLTRIM( ) functions to remove trailing blanks, and the LTRIM( ) or ALLTRIM( ) functions to remove leading blanks, from the string parameter, which ensures that only text characters and any internal blanks are compared.

The MAP( ) function is not affected by the Exact Character Comparisons option (SET EXACT ON/OFF).

You can concatenate two or more fields in the string parameter if you want to search in more than one field in a table. The concatenated fields are treated like a single field that includes leading and trailing spaces from the individual fields, unless you use the ALLTRIM( ) function to remove spaces.

Examples

Example Return value

MAP("ABC Plumbing", "xxx")

T

MAP("045", "9999")

F

The string parameter has only 3 numbers where a minimum of 4 are required.

MAP("XA-123", "XX")

MAP("GC-123", "XX")

MAP("XA-123", "\XX")

MAP("GC-123", "\XX")

T

T

T

F

If the goal is to return ‘T’ for only those values that start with the literal character “X”, followed by any second letter, the format parameter "\XX" ensures that the first “X” in the parameter is interpreted literally and not as a wildcard.

MAP(Invoice_Number, "XX99999")

Returns ‘T’ for all records with invoice numbers that consist of, or that start with, two letters followed by five numbers. Returns ‘F’ otherwise.

MAP(Invoice_Number, "AB12345")

Returns ‘T’ for all records with invoice numbers that are exactly “AB12345”, or that start with “AB12345”. Returns ‘F’ otherwise.

MAP(Invoice_Number, "AB99999")

Returns ‘T’ for all records with invoice numbers that consist of, or that start with, “AB” followed by five numbers. Returns ‘F’ otherwise.

NOT MAP(SSN, "999-99-9999")

Returns ‘T’ for all records that do not match the standard format of social security numbers in the SSN field. Returns ‘F’ otherwise.

Extract records with 10-character product codes and with the leading characters “859-”

You can extract only those records that have product codes at least 10 characters long, and that have the leading characters “859-”, using the following IF statement and the MAP( ) function:

EXTRACT RECORD IF MAP(Product_Code, "85\9-999999") TO "Long_Codes_859"
Related reference
UPPER( ) function
LOWER( ) function
TRIM( ) function
ALLTRIM( ) function
LTRIM( ) function


(C) 2015 ACL Services Ltd. All Rights Reserved.