Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. Creating Custom Functions
  8. Custom Function API Reference
  9. POWERCENTERHELP

Transformation Language Reference

Transformation Language Reference

REG_MATCH

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.
Use the REG_REPLACE function to replace a character pattern in a string with a new character pattern.

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]+’)