REG_MATCH
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.
Syntax
REG_MATCH( subject, pattern )
The following table describes the arguments for this command:
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 quotes. For more information, see REG_EXTRACT. |
Return Value
TRUE if the data matches the pattern.
FALSE 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 Boolean value based on the match:
REG_MATCH (Phone_Number, '(\d\d\d-\d\d\d-\d\d\d\d)' )
Phone_Number | Return Value |
|---|
408-555-1212 | TRUE |
| NULL |
510-555-1212 | TRUE |
92 555 51212 | FALSE |
650-555-1212 | TRUE |
415-555-1212 | TRUE |
831 555 12123 | FALSE |
Tip
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:
REG_MATCH(LAST_NAME, ‘[a-zA-Z]+’)