Pattern Matching Operators

Pattern matching operators compare a string with a format pattern. If NLS is enabled, the result of a match operation depends on the current locale setting of the Ctype and Numeric conventions. Pattern matching operators have the following syntax:

string Match pattern

string is the string to be compared. If string is a null value, the match is false and 0 is returned.

pattern is the format pattern, and can be one of the following codes:

This code...

Matches this type of string...

...

Zero or more characters of any type.

0X

Zero or more characters of any type.

nX

n characters of any type.

0A

Zero or more alphabetic characters.

nA

n alphabetic characters.

0N

Zero or more numeric characters.

nN

n numeric characters.

'string '

Exact text enclosed in double or single quotation marks.

You can specify a negative match by preceding the code with ~ (tilde). For example, ~ 4A matches a string that does not contain four alphabetic characters. If n is longer than nine digits, it is used as a literal string.

If string matches pattern, the comparison returns 1, otherwise it returns 0.

You can specify multiple patterns by separating them with value marks. For example, 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 \\.