Tutorials to .com

Tutorials to .com » Database » Oracle » Oracle10g's SQL Regular Expression Support

Oracle10g's SQL Regular Expression Support

Print View , by: iSee ,Total views: 31 ,Word Count: 688 ,Date: Wed, 26 Aug 2009 Time: 10:00 AM

oracle 8 and Oracle 9i lack of flexibility in SQL Regular Expressions in Oracle 10 g finally been resolved. Oracle database is currently built-in line with the standard POSIX regular expressions.

Four new functions are: REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE. Their usage with the Oracle SQL functions LIKE, INSTR, SUBSTR and REPLACE usage, but they use POSIX regular expressions to replace the old percent sign (%) and the wildcard character (_) character.

POSIX regular expressions by a standard meta-characters (metacharacters) are composed of:

  • '^' Indicates the beginning of the string
  • '$' Indicates the end of the string
  • '.' To express any character
  • Range of characters, for example, '[az]', to express any ASCII lowercase letters, and character class "[[: lower :]]"" equivalent
  • '?' To allow a follow-up character matches zero or one
  • '+' Character to allow a successor to one or more matches
  • '*' Indicates zero or more times

You can use "(m, n)" appeared to specify a precise scope of its meaning is "change from m-to n-"; "(m)" means "exactly m times"; and "(m,)" means " at least m times. " You can also use parentheses combination of a collection of characters, use "|" (vertical bar) that can be replaced. For example, the string '^ ([az] + | [0-9 ]+)$' will match all lowercase letters or numbers from the combination into a string.

REGEXP_LIKE similar with the LIKE operator. If the first argument matches the regular expression it resolves to TRUE. For example, WHERE REGEXP_LIKE (ENAME, '^ J [AO]', 'i') will be ENAME with JA or JO return row start of the data. 'I' parameter to specify the regular expressions are case-sensitive. It also functions in the CHECK constraint and the index specified in the REGEXP_LIKE. For example:


CHECK (REGEXP_LIKE (ENAME ,'^[[: alpha :]]+$'));

This statement makes the ENAME field can contain only alphanumeric characters (ie no spaces or punctuation marks). Attempt to insert or update these data will result in an ORA-2290 exception, or to check the validity of constraints.

REGEXP_INSTR with the INSTR function is similar. It returns a string matches a regular expression of the first sub-string starting position. For example:

SELECT REGEXP_INSTR ( 'The total is $ 400 for your purchase .','$[[: digit :]]+')


This query returns 14, that is, $ 400 at the beginning of the string. Another sub-string can also specify the number of occurrences; start searching position; is to return the position or the return match after match the character position.

REGEXP_SUBSTR return match a regular expression substring. Although the combination of SUBSTR and REGEXP_INSTR and LENGTH can achieve this functionality, but using this function is easier.

SELECT REGEXP_INSTR ( 'one, two, three','[^,]*') FROM DUAL;

This query returns a 'one', will be the first argument as a comma-separated list and returns the first one all the characters before the comma.

REGEXP_REPLACE return the initial parameters are matched substring to replace after the results. For example:

SELECT REGEXP_REPLACE ( 'The temperature is 23 ° F',

'([[: digit :]])+° F ',

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


This query will look for a Fahrenheit temperature and convert it to degrees Celsius. It will return: 'The temperature is -5 ° C'.

(Editor: Ming Ming mingming_ky@126.com TEL: (010) 68476606)

Oracle Tutorial Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.