Oracle Regular Expression
-- Search for "insert into ... " in PL/SQL code

SELECT * FROM user_source
WHERE REGEXP_LIKE(LOWER(text),' insert into po_lines_all_rpt+\s'); 

-- Search/scan for SSN (000-00-0000) pattern in text fields and scrub 
-- such data 

SELECT  ref_str,
  REGEXP_REPLACE(ref_str,'[0-9]{3}-[0-9]{2}-[0-9]{4}','- SSN Scrubbed -') 
FROM (SELECT '&any_str' ref_str FROM DUAL)
WHERE REGEXP_LIKE(ref_str,'[0-9]{3}-[0-9]{2}-[0-9]{4}');

-- REGEXP_LIKE(ref_str,'[[:digit:]]{3}-[:digit:]{2}-[:digit:]{4}') 
-- also works

-- Get only master table (SALES_TRAN) and exclude backup/reload tables
WITH q_tab AS (
  SELECT 'SALES_TRAN_2013'    table_name FROM DUAL UNION
  SELECT 'SALES_TRAN_2014_RL' table_name FROM DUAL UNION
  SELECT 'SALES_TRAN'         table_name FROM DUAL
)
SELECT table_name
FROM  q_tab
WHERE  REGEXP_INSTR(table_name,'[[:digit:]]')=0;

-- Get numeric transaction codes (finalized transactions)
WITH q_tab AS (
  SELECT  100025    tran_cd, 'FINAL'      tran_status FROM DUAL UNION
  SELECT  '100026H' tran_cd, 'HOLD'       tran_status FROM DUAL UNION
  SELECT  '100027I' tran_cd, 'INCOMPLETE' tran_status FROM DUAL UNION
  SELECT  100028    tran_cd, 'FINAL'      tran_status FROM DUAL
) 
SELECT  tran_cd, tran_status
FROM  q_tab
WHERE  REGEXP_LIKE(tran_cd, '^[[:digit:]]*$');


  Other Regular Expresions:   REGEXP_COUNT    REGEXP_SUBSTR

  SQL Functions

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 6th, 2017

  441