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

Name Type Description
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 string.

format can contain wildcard characters, literal characters, or a combination of the two:

"\9\9\9-999-9999"

The following wildcard characters are supported:

  • "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
  • "\" – 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, !, ?)
  • "\\" – specifies a literal backslash

Output

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

Examples

Basic examples

Simple search patterns

Returns T:

MAP("ABC Plumbing", "xxx")

Returns F (string has only 3 numbers where a minimum of 4 are required):

MAP("045", "9999")

Escaping a wildcard

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.

Returns T:

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

Returns F:

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

Fields and patterns

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, "XX99999")

Returns T for all records with invoice numbers that are exactly "AB12345", or that start with "AB12345". Returns F otherwise:

MAP(Invoice_Number, "AB12345")

Returns T for all records with invoice numbers that consist of, or that start with, "AB" followed by five numbers. Returns F otherwise:

MAP(Invoice_Number, "AB99999")

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

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

Advanced examples

Extracting records with 10-character product codes and with the leading characters "859-"

Use an IF statement and the MAP( ) function to extract only those records that have product codes at least 10 characters long, and the leading characters "859-":

EXTRACT RECORD IF MAP(Product_Code, "85\9-999999") TO "Long_Codes_859"

Remarks

When to use MAP( )

Use the MAP( ) function to search for patterns or particular formats in alpha-numeric data. The patterns or formats can be made up of wildcard characters, literal characters, or a combination of both.

Case sensitivity

The MAP( ) function is case-sensitive when comparing two literal characters. For example, "a" is not equivalent to "A".

If string includes character data with inconsistent case, you can use the UPPER( ) function to convert the values to consistent case before using MAP( ).

For example:

MAP(UPPER(Invoice_Number), "AB99999")

Partial matching

MAP( ) supports partial matching in one situation but not in the other.

Partial matching in MAP( ) is not affected by the Exact Character Comparisons option (SET EXACT ON/OFF).

Partial matching supported

Partial matching is supported if the format value is shorter than the string value.

Returns T, because format is 7 characters and string is 9 characters:

MAP("AB1234567", "AB99999")

Note

To return True, the format value must appear at the start of the string value.

Partial matching not supported

Partial matching is not supported if the format value is longer than the string value.

Returns F, because format is 7 characters and string is 6 characters:

MAP("AB1234", "AB99999")

If format is longer than string, the result is always False.

Accounting for blank spaces

Blank spaces are treated as characters, and can be accounted for in either of two ways:

  • match blanks literally, by including blanks in the format value at the appropriate position
  • use the wildcard "?" , which matches any character, including blanks

If required, you can use the TRIM( ), LTRIM( ), or ALLTRIM( ) functions to remove leading or trailing blanks from string, which ensures that only text characters and any internal blanks are compared.

Concatenating fields

You can concatenate two or more fields in string 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 blanks from the individual fields, unless you use the ALLTRIM( ) function to remove them.