Start of change

REGEXP_LIKE

The REGEXP_LIKE scalar function returns an INTEGER value of 0 or 1 indicating if the regular expression pattern is found in a string.

FL 504

Start of change
Passthrough-only expression: This function is passthrough-only and cannot run on Db2 for z/OS® without acceleration. For information about invoking this function, see Accelerating queries with passthrough-only expressions.
End of change

Db2 for z/OS casts the result of this function to an INTEGER (1 = 'true', 0 = 'false'). Db2 for z/OS currently does not support Boolean functions or data types.

Read syntax diagramSkip visual syntax diagramREGEXP_LIKE(source-string ,pattern-expression ,start ,flags ,CODEUNITS32,CODEUNITS16OCTETS )

The schema is SYSIBM.

source-string
An expression that specifies the string in which the search is to take place. This expression must return a built-in character string, graphic string, numeric value, Boolean value, or datetime value. A numeric, Boolean, or datetime value is implicitly cast to VARCHAR before the function is evaluated. A character string cannot specify the FOR BIT DATA attribute.
pattern-expression
An expression that specifies the regular expression string that is the pattern for the search. This expression must return a built-in character string, graphic string, numeric value, Boolean value, or datetime value. A numeric, Boolean, or datetime value is implicitly cast to VARCHAR before the function is evaluated. A character string cannot specify the FOR BIT DATA attribute.
start
An expression that specifies the position within source-string at which the search is to start. The expression must return a built-in character string, graphic string, Boolean, or numeric value. If the value is not of type INTEGER, it is implicitly cast to INTEGER before the function is evaluated. The value of the integer must be greater than or equal to 1. If OCTETS is specified and the source string is graphic data, the value of the integer must be odd. The default start value is 1. See parameter description for CODEUNITS16, CODEUNITS32, or OCTETS for the string unit that applies to the start position.
flags
An expression that specifies flags that controls aspects of the pattern matching. The expression must return a built-in character string that does not specify the FOR BIT DATA attribute. The string can include one or more valid flag values and the combination of flag values must be valid. An empty string is the same as the value 'c'. The default flag value is 'c'.
Table 1. Supported flag values
Flag value Description
c Specifies that matching is case-sensitive. This flag is the default value if 'c' or 'i' is not specified. This value must not be specified with a value of 'i'.
i Specifies that matching is case insensitive. This value must not be specified with a value of 'c'.
m Specifies that the input data can contain more than one line. By default, the '^' in a pattern matches only the start of the input string; the '$' in a pattern matches only the end of the input string. If this flag is set, "^" and "$" also matches at the start and end of each line within the input string.
n Specifies that the '.' character in a pattern matches a line terminator in the input string. By default, the '.' character in a pattern does not match a line terminator. A carriage-return and line-feed pair in the input string behaves as a single-line terminator and matches a single "." in a pattern.
s Specifies that the '.' character in a pattern matches a line terminator in the input string. This value is a synonym for the 'n' value.
x Specifies that white space characters in a pattern are ignored, unless escaped.
CODEUNITS16, CODEUNITS32, or OCTETS
Specifies the string unit of the start value:
  • CODEUNITS16 specifies that the start value is expressed in 16-bit UTF-16 code units.
  • CODEUNITS32 specifies that the start value is expressed in 32-bit UTF-32 code units. This is the default.
  • OCTETS specifies that the start value is expressed in bytes.

If the string unit is specified as CODEUNITS16 or OCTETS, and if the string unit of the source string is CODEUNITS32, an error is returned.

For more information, see String unit specifications.

The result of the function is an INTEGER value of 0 or 1. If the pattern expression is found, the result is true. If the pattern expression is not found, the result is false. If the value of any of the arguments is null, the result is unknown.

Examples

Example 1:
Select the employee number where the last name is spelled LUCCHESSI, LUCHESSI, or LUCHESI from the EMPLOYEE table without considering upper or lower case letters.
   SELECT EMPNO FROM EMPLOYEE
      WHERE REGEXP_LIKE(LASTNAME,'luc+?hes+?i','i') = 1
The result is 1 row with EMPNO value '000110'.
Exmaple 2:
Select any invalid product identifier values from the PRODUCT table. The expected format is 'nnn-nnn-nn' where 'n' is a digit 0 - 9.
SELECT PID FROM PRODUCT 
   WHERE NOT REGEXP_LIKE(pid,'[0-9]{3}-[0-9]{3}-[0-9]{2}') = 1
The result is 0 rows because all the product identifiers match the pattern.
End of change