SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to write regular expression to find desired string piece

mennanJul 4 2010 — edited Jul 5 2010

Hi All,

Suppose that i have following string piece:

name:ali#lastname:kemal#name:mehmet#lastname:cemal

I need

ali
mehmet

I use following statement

SQL> select lst, regexp_replace(lst,'(name:)(.*)(lastname)(.*)','\2',1,1) nm from (
  2    select 'name:ali#lastname:kemal#name:mehmet#lastname:cemal' as lst from dual
  3  );
 
LST                                                NM
-------------------------------------------------- --------------------------------------------------------------------------------
name:ali#lastname:kemal#name:mehmet#lastname:cemal ali#lastname:kemal#name:mehmet#
 
SQL>

But it does not return names correctly. When i change 5th parameter(occurence) of regexp_replace built-in function(e.g. 1,2), i may get ali and mehmet respectiveley.

Any ideas about regexp?

Note : I can use PL/SQL instr/substr for this manner; but i do not want to use them. I need regexp.

Regards...
Mennan

Frank Kulash

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.

Solomon Yakobson

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.

Aketi Jyuuzou

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

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 1 2010

Added on Jul 4 2010

3 comments

1,635 views

SQL & PL/SQL

How to write regular expression to find desired string piece

Comments

Post Details