Using REGEXP_LIKE Condition in Oracle

This article is written about how to use the Oracle REGEXP_LIKE circumstance (to perform ordinary expression matching) with syntax and examples.

  • Not to be careworn with the LIKE condition which performs simple sample matching.

Description

The Oracle REGEXP_LIKE condition lets in you to perform normal expression matching in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the REGEXP_LIKE situation in Oracle/PLSQL is:

REGEXP_LIKE ( expression, pattern [, match_parameter ] )

Parameters or Arguments

expression A character expression such as a column or field. It can be a VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB or NCLOB data type. pattern The everyday expression matching information. It can be a combination of the following: Value Description ^ Matches the establishing of a string. If used with a match_parameter of ‘m’, it matches the start of a line anywhere inside expression. $ Matches the quit of a string. If used with a match_parameter of ‘m’, it matches the stop of a line anywhere within expression. * Matches zero or greater occurrences. + Matches one or extra occurrences. ? Matches zero or one occurrence. . Matches any persona barring NULL. | Used like an “OR” to specify more than one alternative. [ ] Used to specify a matching list where you are making an attempt to suit any one of the characters in the list. [^ ] Used to specify a nonmatching list where you are attempting to suit any character except for the ones in the list. ( ) Used to team expressions as a subexpression. {m} Matches m times. {m,} Matches at least m times. {m,n} Matches at least m times, however no greater than n times. \n n is a number between 1 and 9. Matches the nth subexpression discovered within ( ) before encountering \n. [..] Matches one collation component that can be extra than one character. [::] Matches persona classes. [==] Matches equivalence classes. \d Matches a digit character. \D Matches a nondigit character. \w Matches a word character. \W Matches a nonword character. \s Matches a whitespace character. \S suits a non-whitespace character. \A Matches the establishing of a string or fits at the cease of a string earlier than a newline character. \Z Matches at the quit of a string. *? Matches the previous sample zero or more occurrences. +? Matches the previous sample one or greater occurrences. ?? Matches the preceding sample zero or one occurrence. {n}? Matches the preceding sample n times. {n,}? Matches the previous sample at least n times. {n,m}? Matches the previous pattern at least n times, but no longer more than m times. match_parameter Optional. It approves you to modify the matching behavior for the REGEXP_LIKE condition. It can be a combination of the following: Value Description ‘c’ Perform case-sensitive matching. ‘i’ Perform case-insensitive matching. ‘n’ Allows the period character (.) to fit the newline character. By default, the duration is a wildcard. ‘m’ expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the role of these characters in expression. By default, expression is assumed to be a single line. ‘x’ Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

Note

The REGEXP_LIKE condition uses the input personality set to evaluate strings. If you specify match_parameter values that conflict, the REGEXP_LIKE situation will use the remaining cost to smash the conflict. If the match_parameter is omitted, the REGEXP_LIKE condition will use the case-sensitivity as determined with the aid of the NLS_SORT parameter. See additionally the Oracle LIKE condition.

Example – Match on more than one alternative

The first Oracle REGEXP_LIKE condition example that we will appear at includes using the | pattern.

Let’s give an explanation for how the | pattern works in the Oracle REGEXP_LIKE condition. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, 'Anders(o|e|a)n');

This REGEXP_LIKE instance will return all contacts whose last_name is either Anderson, Andersen, or Andersan. The | sample tells us to appear for the letter “o”, “e”, or “a”.

Example – Match on beginning

Next, let’s use the REGEXP_LIKE condition to in shape on the commencing of a string. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '^A(*)');

This REGEXP_LIKE instance will return all contacts whose last_name starts offevolved with ‘A’.

Example – Match on end

Next, let’s use the REGEXP_LIKE circumstance to in shape on the quit of a string. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '(*)n$');

This REGEXP_LIKE instance will return all contacts whose last_name ends with ‘n’.