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