Returns whether a value matches a regular expression pattern. This lets you validate data patterns, such as IDs, telephone numbers, postal codes, and state names.
Note: Use the REG_REPLACE function to replace a character pattern in a string with a new character pattern.
Syntax
REG_MATCH( subject,pattern )
Argument
Required/
Optional
Description
subject
Required
String datatype. Passes the value you want to match against the regular expression pattern.
pattern
Required
String datatype. Regular expression pattern that you want to match. You must use perl compatible regular expression syntax. Enclose the pattern in single quotation marks. For more information, see REG_EXTRACT.
Return Value
1 if the data matches the pattern.
0 if the data does not match the pattern.
NULL if the input is a null value or if the pattern is NULL.
Example
You might use REG_MATCH in an expression to validate telephone numbers. For example, the following expression matches a 10-digit telephone number against the pattern and returns a numerical value based on the match:
You can also use REG_MATCH for the following tasks:
•To verify that a value matches a pattern. This use is similar to the SQL LIKE function.
•To verify that values are characters. This use is similar to the SQL IS_CHAR function.
To verify that a value matches a pattern, use a period (.) and an asterisk (*) with the REG_MATCH function in an expression. A period matches any one character. An asterisk matches 0 or more instances of values that follow it.
For example, use the following expression to find account numbers that begin with 1835:
REG_MATCH(ACCOUNT_NUMBER, ‘1835.*’)
To verify that values are characters, use a REG_MATCH function with the regular expression [a-zA-Z]+. a-z matches all lowercase characters. A-Z matches all uppercase characters. The plus sign (+) indicates that there should be at least one character.
For example, use the following expression to verify that a list of last names contain only characters: