Using Regular Expressions with Oracle Database - two examples

Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

denis@test10g> ho ls

denis@test10g> @regexp_like.sql
denis@test10g> rem regexp_like.sql
denis@test10g> rem   - familar with REGEXP_LIKE
denis@test10g> rem   using regular expression to enfore data integrity
denis@test10g> 
denis@test10g> drop table t;

Table dropped.

denis@test10g> create table t
  2  (
  3    l_name varchar2(30),
  4    p_number varchar2(30)
  5    constraint p_number_format
  6       check ( REGEXP_LIKE(p_number, '^\(\d{3}\) \d{3}-\d{4}$' ) )
  7  );

Table created.

denis@test10g> 
denis@test10g> -- first two statements use valid phone number format
denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '(650) 555-5555'   );

1 row created.

denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '(215) 555-3427'   );

1 row created.

denis@test10g> -- remaining statements generate check contraint errors
denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '650 555-5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '650 555 5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '650-555-5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( '(650)555-5555'    );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> INSERT INTO t (p_number)
  2    VALUES( ' (650) 555-5555'  );
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> /
INSERT INTO t (p_number)
*
ERROR at line 1:
ORA-02290: check constraint (DENIS.P_NUMBER_FORMAT) violated


denis@test10g> 
denis@test10g> ho ls

denis@test10g> @back_references.sql
denis@test10g> rem back_references.sql
denis@test10g> rem
denis@test10g> rem  Using Back Referencs to Reposition Characters
denis@test10g> rem  -- back references store matched subexpressions in a temporary buffer,
denis@test10g> rem    thereby enabling you to reposition characters
denis@test10g> rem
denis@test10g> rem  Explanation of the Regular Expression Elements in the Example
denis@test10g> rem
denis@test10g> rem   ^    Matches the beginning of the string.
denis@test10g> rem   $    Matches the end of the string.
denis@test10g> rem  (\S+)  Matches one or more non-space characters. The parentheses are not escaped so
denis@test10g> rem       they function as a grouping expression.
denis@test10g> rem  \s    Matches a whitespace character.
denis@test10g> rem  \1    Substitutes the first subexpression, that is, the first group of parentheses
denis@test10g> rem       in the matching pattern.
denis@test10g> rem  \2    Substitutes the second subexpression.
denis@test10g> rem  \3    Substitutes the third subexpression.
denis@test10g> rem   ,    Inserts a comma character.
denis@test10g> 
denis@test10g> 
denis@test10g> create table famous_people
  2  ( names varchar2(30)) ;

Table created.

denis@test10g> 
denis@test10g> -- populate table with data
denis@test10g> INSERT INTO famous_people
  2    VALUES ('John Quincy Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES ('Harry S. Truman');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES ('John Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES (' John Quincy Adams');

1 row created.

denis@test10g> INSERT INTO famous_people
  2    VALUES ('John_Quincy_Adams');

1 row created.

denis@test10g> COMMIT;

Commit complete.

denis@test10g> 
denis@test10g> 
denis@test10g> col "names" format a40
denis@test10g> col "names after regexp" format a40
denis@test10g> 
denis@test10g> SELECT names "names",
  2    REGEXP_REPLACE(names,
  3          '^(\S+)\s(\S+)\s(\S+)$',
  4          '\3, \1 \2')
  5    AS "names after regexp"
  6  FROM famous_people;

names                                    names after regexp
---------------------------------------- ----------------------------------------
John Quincy Adams                        Adams, John Quincy
Harry S. Truman                          Truman, Harry S.
John Adams                               John Adams
 John Quincy Adams                        John Quincy Adams
John_Quincy_Adams                        John_Quincy_Adams

denis@test10g> 
denis@test10g> drop table famous_people;

Table dropped.

denis@test10g> 
denis@test10g> spool off