Oracle Database Discussions

Hi, Mennan,

You can nest REGEXP_SUBSTR withing REGEXP_REPLACE to get the n-th occurrence, like this:

SELECT	lst
, 	REGEXP_REPLACE ( REGEXP_SUBSTR ( lst
		       		       , 'name:[^#]*#lastname'
				       , 1
				       , n
				       )
		       , 'name:(.*)#lastname'
		       , '\1'
		       ) 	AS nm 

If the pattern occurs fewer than n times, the expression above returns NULL.

Assuming string is always a set of name and last name:

with t as (
           select 'name:ali#lastname:kemal#name:mehmet#lastname:cemal' str from dual
          )
select  regexp_replace(regexp_substr('#' || str,'#name:[^#]*',1,level),'^#name:') name
  from  t
  connect by level <= length(regexp_replace('#' || str,'[^#]')) / 2
/

NAME
--------
ali
mehmet

SQL> 

SY.

I like recusrive with clause B-)
And in this case,6th parameter of RegExp_SubStr is really useful ;-)

col extStr for a20

with work(Val) as(
select 'name:ali#lastname:kemal#name:mehmet#lastname:cemal'
from dual),
rec(Val,extStr,LV) as(
select Val,
RegExp_SubStr(Val,'(^|#)name:([^#]+)',1,1,null,2),1
  from work
union all
select Val,
RegExp_SubStr(Val,'(^|#)name:([^#]+)',1,LV+1,null,2),LV+1
  from rec
 where RegExp_Count(Val,'(^|#)name:[^#]+') >= LV+1)
select extStr from rec;

EXTSTR
-------
ali
mehmet