Questions on Regular Expression support

Reference:http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: What is Regular Expression (REGEXP) in the Oracle 10g Database?
A: It is a method for simple and complex patterns for searching and manipulating a text. You can search, extract, format, and manipulate a text in the database. At the beginning, it appears that the syntax is not very intuitive but by second look, it may look easy. The technique more reflects as UNIX style regular expressions.

Q: What are functions of REGEXP?
A: Interfaces: Oracle Regular Expressions are implemented by the following functions available in SQL and PL/SQL.
REGEXP_LIKE,
REGEXP_REPLACE,
REGEXP_INSTR, and
REGEXP_SUBSTR

Q: Consider a simple query to convert the ‘McLean’ city name to a more readable format (Mc Lean). You should look for any instance for a lower case letter immediately followed by an upper case letter. Your query should record these two letters in backreferences by using subexpressions, then replaces the first one, followed by a space, then followed by the second letter.
A:
SQL> SELECT
REGEXP_REPLACE(‘McLean’,
‘([[:lower:]])([[:upper:]])’, ‘\1 \2’) as “City”
FROM dual;

Q: How to use REGULAR EXPRESSIONS in Oracle
A: Keep this in your mind that these functions support CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR, and NCLOB datatypes.

Q: What does the REGEXP_LIKE function?
A: It returns a Boolean indicating whether the pattern matched or not.

Q: Consider to write an expression that could search for common inflections of the verb ‘try’.
A: The following regular expression will match try, trying, tried, and tries.
SQL> SELECT
REGEXP_LIKE (‘We are trying to make the subject easier.’,
‘tr(y(ing)? (ied) (ies))’) as REGEXT_SAMPLE
FROM dual;

Q: What does the REGEXP_SUBSTR function?
A: It returns the actual data that matches the specified pattern.

Q: Consider to write an expression that could return the ‘trying’ specified pattern.
A:
SQL> SELECT
REGEXP_SUBSTR (‘We are trying to make the subject easier.’,
‘tr(y(ing)? (ied) (ies))’) as REGEXT_SAMPLE
FROM dual;

Q: What does the REGEXP_INSTR function?
A: It returns the character position of either the beginning or end of the match.

Q: Consider to write an expression that could return the position of ‘trying’ specified pattern.
A:
SQL> SELECT
REGEXP_INSTR (‘We are trying to make the subject easier.’,
‘tr(y(ing)? (ied) (ies))’) as REGEXT_SAMPLE
FROM dual;

Q: What does the REGEXP_REPLACE function?
A: It looks for an occurrence of a regular expression and replaces it with the contents of a supplied text literal.

Q: Query a list of all employees’ name that hired between 1996 and 1999.
A:
SQL> SELECT ename FROM emp
WHERE REGEXP_REPLACE
(TO_CHAR(hire_date, ‘YYYY’), ‘^199[6-9]$’);
You used ‘^’ to indicate that the beginning of the line has to be 199, and [-] with $ to specify range of valid characters.

Q: What is occurrence in the REGEXP functions?
A: All functions take an occurrence that specifies you require the nth matching expression in REGEXP_SUBSTR and REGEXP_INSTR, the default for which is 1.

Q: Consider extracting the third field being the Oracle system identification in a column.
A:
SQL> SELECT
REGEXP_SUBSTR(‘system/password@myhost:1521:mysid’,
‘[^:]+’, 1, 3) as “SID name”
FROM dual;