fastread homefastrread library fastread menu

Oracle/PLSQL : REGEXP_LIKE Condition

Tutorial by:      Date: 2016-04-15 00:46:49

❰ Previous Next ❱

This Oracle tutorial explains how to use the Oracle REGEXP_LIKE condition (to perform regular expression matching) with syntax and examples.

* Not to be confused with the LIKE condition which performs simple pattern matching.

Description

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

Syntax

The syntax for the REGEXP_LIKE condition 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 regular expression matching information. It can be a combination of the following:

Value

Description

^

Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.

$

Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression.

*

Matches zero or more occurrences.

+

Matches one or more occurrences.

?

Matches zero or one occurrence.

.

Matches any character except NULL.

|

Used like an "OR" to specify more than one alternative.

[ ]

Used to specify a matching list where you are trying to match any one of the characters in the list.

[^ ]

Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.

( )

Used to group expressions as a subexpression.

{m}

Matches m times.

{m,}

Matches at least m times.

{m,n}

Matches at least m times, but no more than n times.

\n

n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.

[..]

Matches one collation element that can be more than one character.

[::]

Matches character 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

matches a non-whitespace character.

\A

Matches the beginning of a string or matches at the end of a string before a newline character.

\Z

Matches at the end of a string.

*?

Matches the preceding pattern zero or more occurrences.

+?

Matches the preceding pattern one or more occurrences.

??

Matches the preceding pattern zero or one occurrence.

{n}?

Matches the preceding pattern n times.

{n,}?

Matches the preceding pattern at least n times.

{n,m}?

Matches the preceding pattern at least n times, but not more than m times.

match_parameter

Optional. It allows 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 match the newline character. By default, the period 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 position of those 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 character set to evaluate strings.
  • If you specify match_parameter values that conflict, the REGEXP_LIKE condition will use the last value to break the conflict.
  • If the match_parameter is omitted, the REGEXP_LIKE condition will use the case-sensitivity as determined by the NLS_SORT parameter.
  • See also the Oracle LIKE condition.

Example - Match on more than one alternative

The first Oracle REGEXP_LIKE condition example that we will look at involves using the | pattern.

Let's explain 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 example will return all contacts whose last_name is either Anderson, Andersen, or Andersan. The | pattern tells us to look for the letter "o", "e", or "a".

Example - Match on beginning

Next, let's use the REGEXP_LIKE condition to match on the beginning of a string. For example:

SELECT last_name

FROM contacts

WHERE REGEXP_LIKE (last_name, '^A(*)');

This REGEXP_LIKE example will return all contacts whose last_name starts with 'A'.

Example - Match on end

Next, let's use the REGEXP_LIKE condition to match on the end of a string. For example:

SELECT last_name

FROM contacts

WHERE REGEXP_LIKE (last_name, '(*)n$');

This REGEXP_LIKE example will return all contacts whose last_name ends with 'n'.

 

❰ Previous Next ❱


Oracle/PLSQL

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website