Regular Expressions

Oracle 10g supports regular expressions in SQL and PL/SQL with the following functions:

• REGEXP_INSTR - Similar to INSTR except it uses a regular expression rather than a literal as the search string.
• REGEXP_LIKE - Similar to LIKE except it uses a regular expression as the search string.
• REGEXP_REPLACE - Similar to REPLACE except it uses a regular expression as the search string.
• REGEXP_SUBSTR - Returns the string matching the regular expression. Not really similar to SUBSTR.

The following examples show how these functions can be used with a simple regular expression ('[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}') for basic credit card validation:

SET SERVEROUTPUT ON DECLARE   l_text           VARCHAR2(100) := 'My credit card details are: 1234 1234 1234 1234';   l_regular_expr   VARCHAR2(50)  := '[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}';     l_credit_card_1  VARCHAR2(50)  := '1234 1234 1234 1234';   l_credit_card_2  VARCHAR2(50)  := '123c 1234 1234 1234'; BEGIN   -- REGEXP_INSTR   IF REGEXP_INSTR(l_text, l_regular_expr) > 0 THEN     DBMS_OUTPUT.put_line('REGEXP_INSTR: Your input contains credit card details, this is a security risk!');   END IF;     -- REGEXP_LIKE   IF REGEXP_LIKE(l_credit_card_1, l_regular_expr) THEN     DBMS_OUTPUT.put_line('REGEXP_LIKE: Good Credit Card: ' || l_credit_card_1);   END IF;   IF NOT REGEXP_LIKE(l_credit_card_2, l_regular_expr) THEN     DBMS_OUTPUT.put_line('REGEXP_LIKE: Bad Credit Card : ' || l_credit_card_2);   END IF;     -- REGEXP_REPLACE   DBMS_OUTPUT.put_line('REGEXP_REPLACE: Before: ' || l_text);   DBMS_OUTPUT.put_line('REGEXP_REPLACE: After : ' || REGEXP_REPLACE(l_text, l_regular_expr, '**** **** **** ****'));     -- REGEXP_SUBSTR   DBMS_OUTPUT.put_line('REGEXP_SUBSTR: Matching String : ' || REGEXP_SUBSTR(l_text, l_regular_expr)); END; /

Building regular expressions to match your requirements can get a little confusing and this is beyond the scope of this article.

Fonte: http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php