SQL Snippets ™: Tutorials for Oracle Developers

--------------------------------------------------------------------------------
-- setup for pattern matching examples
--------------------------------------------------------------------------------

create table targets ( target varchar2(15) );

create procedure set_target( p_target varchar2 ) as
begin
  delete from targets ;
  insert into targets values ( p_target ) ;
end;
/

create procedure add_target( p_target varchar2 ) as
begin
  insert into targets values ( p_target );
end;
/


create table patterns ( pattern varchar2(25), sort_key number );

create procedure set_pattern( p_pattern varchar2 ) as
begin
  delete from patterns;
  insert into patterns values ( p_pattern, 1 );
end;
/

create procedure add_pattern( p_pattern varchar2 ) as
begin
  insert into patterns
  values
  ( p_pattern ,
    ( select count(*) + 1 from patterns )
  );
end;
/


create view test_results as
select
  pattern ,
  target ,
  case
    when regexp_like( target, pattern ) then 'Y'
    else 'N'
    end
    as match,
  regexp_substr( target, pattern ) as matched_value ,
  regexp_instr( target, pattern ) as position
from
  patterns,
  targets
order by
  patterns.sort_key,
  target
;

column pattern         format a15
column target          format a10
column match           format a5
column matched_value   format a15
column modifier        format a8
column replaced_target format a15
column position        format 999
column substring       format a15

break on pattern skip 1

set null     '(null)'
set linesize 100


--------------------------------------------------------------------------------
-- setup for the section on POSIX character classes
--------------------------------------------------------------------------------

create table character_class_map
( data    varchar2(2)  ,
  symbol  varchar2(3)  ,
  dec     number       ,
  hex     varchar2(4)  ,
  upper   varchar2(1)  ,
  lower   varchar2(1)  ,
  alpha   varchar2(1)  ,
  digit   varchar2(1)  ,
  xdigit  varchar2(1)  ,
  alnum   varchar2(1)  ,
  blank   varchar2(1)  ,
  space   varchar2(1)  ,
  cntrl   varchar2(1)  ,
  punct   varchar2(1)  ,
  graph   varchar2(1)  ,
  print   varchar2(1)
);


declare

  v_data      varchar2(2) ;
  v_hex_value varchar2(4) ;
  v_symbol    varchar2(3) ;

begin

  for  i  in  0  ..  255
  loop

      v_hex_value := to_char( i, 'FM000X' ) ;
      v_data      := unistr( '\' || v_hex_value  ) ;

      v_symbol :=
        case i
        when 0   then 'NUL'  when 1   then 'SOH'  when 2   then 'STX'
        when 3   then 'ETX'  when 4   then 'EOT'  when 5   then 'ENQ'
        when 6   then 'ACK'  when 7   then 'BEL'  when 8   then 'BS'
        when 9   then 'TAB'  when 10  then 'LF'   when 11  then 'VT'
        when 12  then 'FF'   when 13  then 'CR'   when 14  then 'SO'
        when 15  then 'SI'   when 16  then 'DLE'  when 17  then 'DC1'
        when 18  then 'DC2'  when 19  then 'DC3'  when 20  then 'DC4'
        when 21  then 'NAK'  when 22  then 'SYN'  when 23  then 'ETB'
        when 24  then 'CAN'  when 25  then 'EM'   when 26  then 'SUB'
        when 27  then 'ESC'  when 28  then 'FS'   when 29  then 'GS'
        when 30  then 'RS'   when 31  then 'US'   when 127 then 'DEL'
        when 128 then 'XXX'  when 129 then 'XXX'  when 130 then 'BPH'
        when 131 then 'NBH'  when 132 then 'IND'  when 133 then 'NEL'
        when 134 then 'SSA'  when 135 then 'ESA'  when 136 then 'HTS'
        when 137 then 'HTJ'  when 138 then 'VTS'  when 139 then 'PLD'
        when 140 then 'PLU'  when 141 then 'RI'   when 142 then 'SS2'
        when 143 then 'SS3'  when 144 then 'DCS'  when 145 then 'PU1'
        when 146 then 'PU2'  when 147 then 'STS'  when 148 then 'CCH'
        when 149 then 'MW'   when 150 then 'SPA'  when 151 then 'EPA'
        when 152 then 'SOS'  when 153 then 'XXX'  when 154 then 'SCI'
        when 155 then 'CSI'  when 156 then 'ST'   when 157 then 'OSC'
        when 158 then 'PM'   when 159 then 'APC'  when 173 then 'SHY'
        else v_data
        end
      ;

      insert into character_class_map
      ( data ,
        symbol ,
        dec ,
        hex
      ) values
      ( v_data ,
        v_symbol ,
        i ,
        v_hex_value
      );

  end loop;

end;
/

update character_class_map set upper = 'Y' where regexp_like( data, '[[:upper:]]' );
update character_class_map set lower = 'Y' where regexp_like( data, '[[:lower:]]' );
update character_class_map set alpha = 'Y' where regexp_like( data, '[[:alpha:]]' );
update character_class_map set digit = 'Y' where regexp_like( data, '[[:digit:]]' );
update character_class_map set xdigit= 'Y' where regexp_like( data, '[[:xdigit:]]');
update character_class_map set alnum = 'Y' where regexp_like( data, '[[:alnum:]]' );
update character_class_map set blank = 'Y' where regexp_like( data, '[[:blank:]]' );
update character_class_map set space = 'Y' where regexp_like( data, '[[:space:]]' );
update character_class_map set cntrl = 'Y' where regexp_like( data, '[[:cntrl:]]' );
update character_class_map set punct = 'Y' where regexp_like( data, '[[:punct:]]' );
update character_class_map set graph = 'Y' where regexp_like( data, '[[:graph:]]' );
update character_class_map set print = 'Y' where regexp_like( data, '[[:print:]]' );
commit;

column symbol format  a6
column dec    format  999
column hex    format  a4
column upper  format  a5
column lower  format  a5
column alpha  format  a5
column digit  format  a5
column xdigit format  a6
column alnum  format  a5
column blank  format  a5
column space  format  a5
column cntrl  format  a5
column punct  format  a5
column graph  format  a5
column print  format  a5