Hi All!
Did you know since 11g the REGEXP_INSTR and REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern?
Also, a new function, REGEXP_COUNT, returns the number of times the search pattern appears in source string.
Below a simple example of each one:
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) FROM dual; REGEXP_INSTR('1234567890','(123)(4(56)(78))',1,1,0,'I',3) --------------------------------------------------------- 5 SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) FROM dual; RE -- 56 SQL> SELECT REGEXP_COUNT('123 123 123 123', '123', 1, 'i') FROM dual; REGEXP_COUNT('123123123123','123',1,'I') ---------------------------------------- 4
Cheers!