MATCH operator
Syntax
string MATCH[ES] patternDescription
Use the MATCH operator or its synonym MATCHES to compare a string expression with a pattern.
pattern is a general description of the format of string. It can consist of text or the special characters X, A, and N preceded by an integer used as a repeating factor. For example, nN is the pattern for strings of n numeric characters.
The following table lists the pattern codes and their definitions:
| Pattern | Definition |
|---|---|
| ... | Any number of any characters (including none). |
| 0X | Any number of any characters (including none). |
| nX | n number of any characters. |
| 0A | Any number of alphabetic characters (including none). |
| nA | n number of alphabetic characters. |
| 0N | Any number of numeric characters (including none). |
| nN | n number of numeric characters. |
| 'text' | Exact text; any literal string (quotation marks required). |
| "text" | Exact text; any literal string (quotation marks required). |
If n is longer than nine digits, it is used as text in a pattern rather than as a repeating factor for a special character. For example, the pattern "1234567890N" is treated as a literal string, not as a pattern of 1,234,567,890 numeric characters.
If the string being evaluated matches the pattern, the expression evaluates as true ( 1 ); otherwise, it evaluates as false ( 0 ). If either string or pattern is the null value, the match evaluates as false.
A tilde ( ~ ) placed immediately before pattern specifies a negative match. That is, it specifies a pattern or a part of a pattern that does not match the expression or a part of the expression. The match is true only if string and pattern are of equal length and differ in at least one character. An example of a negative match pattern is:
"'A'~'X'5NThis pattern returns a value of true if the expression begins with the letter A, which is not followed by the letter X, and which is followed by any five numeric characters. Thus AB55555 matches the pattern, but AX55555, A55555, AX5555, and A5555 do not.
You can specify multiple patterns by separating them with value marks (ASCII CHAR(253) ). The following expression is true if the address is either 16 alphabetic characters or 4 numeric characters followed by 12 alphabetic characters; otherwise, it is false:
ADDRESS MATCHES "16A": CHAR(253): "4N12A"An empty string matches the following patterns: "0A", "0X", "0N", "...", "", '', or \\.
If NLS is enabled, the MATCH operator uses the current values for alphabetic and numeric characters specified in the NLS.LC.CTYPE file.