Regular expressions 11g

This article presents new features of 11g for regular expressions:

  • access to the n-th subexpression in REGEXP_INSTR and REGEXP_SUBSTR functions
  • new function REGEXP_COUNT returns number of times a pattern match is found in an input string

Examples REGEXP_INSTR, REGEXP_SUBSTR:

If we have a simple string: Simple string
and define patter search for it: (Sim)(pl(e s)(tring))

Remember patter can have one or many subexpressions

Expression engine will read this string from left to right and based on patter will return 4 subexpressions. Subexpression is defined by open/close brackets (     ).

(Sim) – 1-st subexpression (Sim)(pl(e s)(tring)) returns: Sim
(ple string) – 2-nd subexpression (Sim)(pl(e s)(tring)) returns: ple string
(e s) – 3-rd subexpression (Sim)(pl(e s)(tring)) returns: e s
(tring) – 4-th subexpression (Sim)(pl(e s)(tring)) returns: tring
As you can see it looks for ope/close brackets from left to right outer/inner pair.

 

--returns starting position of 2-nd subexpression (ple )
SELECT 
  regexp_instr
  ('Simple string',       --source string
   '(Sim)(ple )(string)', --patter search (3 subexpressions)
   1,                     --position to start searching
   1,                     --occurence
   0,                     --return option 0 - starting position 
                          --              1 - ending position
   'i',                   --match option (case insensitive)
   2)                     --subexpression position (ple )
FROM dual;
 
REGEXP_INSTR('SIMPLESTRING','(SIM)(PLE)(STRING)',1,1,0,'I',2)
-------------------------------------------------------------
                                                            4 
--returns ending position of 2-nd subexpression (ple )
SELECT 
  regexp_instr
  ('Simple string',       --source string
   '(Sim)(ple )(string)', --patter search
   1,                     --position to start searching
   1,                     --occurrence
   1,                     --return option 0 - starting position 
                          --              1 - ending position
   'i',                   --match option (case insensitive)
   2)                     --subexpression position (ple )
FROM dual;
 
REGEXP_INSTR('SIMPLESTRING','(SIM)(PLE)(STRING)',1,1,1,'I',2)
-------------------------------------------------------------
                                                            8
--returns starting position of 4-th subexpression (i)
SELECT 
  regexp_instr
  ('Simple string',         --source string
   '(Sim)(pl(e s)tr(i)ng)', --patter search (4 subexpressions)
   1,                       --position to start searching
   1,                       --occurrence
   1,                       --return option 0 - starting position 
                            --              1 - ending position
   'i',                     --match option (case insensitive)
   4)                       --subexpression to return (i)
FROM dual;
 
REGEXP_INSTR('SIMPLESTRING','(SIM)(PL(ES)TR(I)NG)',1,1,1,'I',4)
---------------------------------------------------------------
                                                             12 
--returns 2-nd subexpression (ple )
SELECT 
  regexp_substr
  ('Simple string',       --source string
   '(Sim)(ple )(string)', --patter search (3 subexpressions)
   1,                     --position to start searching
   1,                     --occurrence
   'i',                   --match option (case insensitive)
   2)                     --subexpression position (ple )
FROM dual;
 
REGEXP_SUBSTR('SIMPLESTRING','(SIM)(PLE)(STRING)',1,1,'I',2)
------------------------------------------------------------
ple     
 
--returns 3-rd subexpression (e s) 
SELECT 
  regexp_substr
  ('Simple string',         --source string
   '(Sim)(pl(e s)tr(i)ng)', --patter search (4 subexpressions)
   1,                       --position to start searching
   1,                       --occurrence
   'i',                     --match option (case insensitive)
   3)                       --subexpression to return (i)
FROM dual;
 
REGEXP_SUBSTR('SIMPLESTRING','(SIM)(PL(ES)TR(I)NG)',1,1,'I',3)
--------------------------------------------------------------
e s
 
--returns 4-th subexpression (i)
SELECT 
  regexp_substr
  ('Simple string',         --source string
   '(Sim)(pl(e s)tr(i)ng)', --patter search (4 subexpressions)
   1,                       --position to start searching
   1,                       --occurrence
   'i',                     --match option (case insensitive)
   4)                       --subexpression to return (i)
FROM dual;
 
REGEXP_SUBSTR('SIMPLESTRING','(SIM)(PL(ES)TR(I)NG)',1,1,'I',4)
--------------------------------------------------------------
i

Examples REGEXP_COUNT

Remember it returns the number of times a pattern appears in a string

SELECT regexp_count('How many one 1 2 3 1 4 5 1', '1') FROM dual;
 
REGEXP_COUNT('HOWMANYONE1231451','1')
-------------------------------------
                                    3
 
SELECT regexp_count('How many twelve 12 2 3 121 4 5 1', '12') 
FROM dual;
 
REGEXP_COUNT('HOWMANYTWELVE1223121451','12')
--------------------------------------------
                                           2
 
SELECT regexp_count('How many one or two 12 2 3 121 4 5 1', '[12]') 
FROM dual;
 
REGEXP_COUNT('HOWMANYONEORTWO1223121451','[12]')
------------------------------------------------
                                               7

Have a fun

Tomasz

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *