Study on Regular Expression in Oracle

Source: Internet
Author: User

Regular Expression)

Oracle 10 Gb supports four new functions of Regular Expressions: regexp_like, regexp_instr, regexp_substr, and regexp_replace.

They are used with Oracle SQL functions like, instr, substr, and replace, but they use POSIX Regular Expressions instead of the old percent sign (%) and wildcard character.


POSIX regular expressions are composed of standard metacharacters:

'^' Matches the start position of the input string and is used in the square brackets expression. In this case, this character set is not accepted.

'$' Matches the end position of the input string. If the multiline attribute of the Regexp object is set, $ also matches 'n' or 'R '.

'.' Matches any single character except linefeed n.

'? 'Match the previous subexpression zero or once.

'+' Matches the previous subexpression once or multiple times.

'*' Matches the previous subexpression zero or multiple times.

'|' Indicates an option between the two items. Example '^ ([A-Z] + | [0-9] +) $' indicates a string composed of all lowercase letters or numbers.

'()' Indicates the start and end positions of a subexpression.

'[]' Indicates a bracket expression.

'{M, n}' indicates the exact number of occurrences. M = <number of occurrences <= N, '{m}' indicates M occurrences, '{M ,} 'indicates that at least m occurs.

Num matches num, where num is a positive integer. References to the obtained matching.



Character cluster:

[[: Alpha:] any letter.

[[: Digit:] any number.

[[: Alnum:] Any letter or number.

[[: Space:] any white characters.

[[: Upper:] Any uppercase letter.

[[: Lower:] Any lowercase letter.

[[: Punct:] Any punctuation marks.

[[: Xdigit:] Any hexadecimal number, which is equivalent to [0-9a-fa-f].


Operation priority of various operators

Escape Character

(),(? :),(? =), [] Parentheses and square brackets

*, + ,?, {N}, {n ,}, {n, m} qualifier

^, $, Anymetacharacter location and Sequence

| "Or" Operation



Regexp_like is similar to the like operator. If the first parameter matches the regular expression, it is parsed to true. For example, where regexp_like (ename, '^ J [AO]', 'I') returns a row of data when the ename starts with Ja or Jo. The 'I' parameter specifies that the regular expression is case sensitive. You can also specify regexp_like in the check constraints and function indexes. For example:

Alter table emp add constraint regex01

Check (regexp_like (ename, '^ [[: Alpha:] + $ '));

This statement allows the ename field to only contain letters and numbers (that is, there is no space or punctuation ). An attempt to insert or update the data will cause a ORA-2290 exception or check the validity of the constraint.

The regexp_instr function is similar to the instr function. It returns the starting position of the first substring that matches a regular expression in a string. For example:

Select regexp_instr ('the total is $400 for your purchase. ',' $ [[: digit:] + ')

From dual;

This query returns 14, that is, $400 at the start of the string. In addition, it can also indicate the number of times the substring appears; the position where the search starts; whether to return the matched position or the position of the matched character.

Regexp_substr returns a substring that matches a regular expression. Although substr, regexp_instr, and length can be used in combination, it is simpler to use this function.

Select regexp_instr ('one, Two, Three ',' [^,] * ') from dual;

This query returns 'one'. The first parameter is considered as a comma-separated list and all characters before the first comma are returned.

Regexp_replace returns the result after the initial parameter is replaced by a matched substring. For example:

Select regexp_replace ('the temperature is 23 °f ',

'([[: Digit:]) + °f ',

('1'-32) * 5/9 | '°c ')

From dual;

This query will look for an Fahrenheit temperature and convert it to degrees Celsius. It returns 'the temperature is-5 °C '.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.