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:
|
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.