Skip to Main Content
Forums

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.

Reg Exp Help

NicloeiWJul 11 2010 — edited Jul 14 2010
Hi Experts,

I have following requirement,

String	Output
A^B^^C		A^B^C
^A				A
^B^^C			B^C
^B^^C^^A	B^C^A
^C^				C
^^C^^			C

can this be achieved with reg exp, or i need to use pl/sql with substring and instr looping

plz assist me?

Regards
nic
Edited by: Nicloei W on Jul 11, 2010 7:29 PM

Edited by: Nicloei W on Jul 11, 2010 7:33 PM
This post has been answered by fsitja on Jul 11 2010
Jump to Answer

Comments

fsitja
Answer
Maybe this: 
SQL> with t as (
  2  select 'A^B^^C' str from dual union all
  3  select '^A' from dual union all
  4  select '^B^^C' from dual union all
  5  select '^B^^C^^A' from dual union all
  6  select '^C^' from dual union all
  7  select '^^C^^' from dual)
  8  -- end of sample data
  9  select str, rtrim(ltrim(regexp_replace(str, '(\^)+', '\1'), '^'), '^') output
 10    from t;
 
STR      OUTPUT
-------- --------------------------------------------------------------------------------
A^B^^C   A^B^C
^A       A
^B^^C    B^C
^B^^C^^A B^C^A
^C^      C
^^C^^    C
 
6 rows selected
 
SQL>
Marked as Answer by NicloeiW · Sep 27 2020
NicloeiW
Hi Hi,

Its working fine, if possible could you explain the logic u used in brief esp the reg replace and metacharacter

regards
nic
Aketi Jyuuzou
I like Regex B-) 
col output for a20

with t as (
select 'A^B^^C' str from dual union all
select '^A' from dual union all
select '^B^^C' from dual union all
select '^B^^C^^A' from dual union all
select '^C^' from dual union all
select '^^C^^' from dual)
select str,
RegExp_Replace(trim(both '^' from str),'\^+','^') output
  from t;

STR       OUTPUT
--------  ------
A^B^^C    A^B^C 
^A        A     
^B^^C     B^C   
^B^^C^^A  B^C^A 
^C^       C     
^^C^^     C
Introduction to regular expressions part4
1097730
1 person found this helpful
fsitja
Hi,

Regexp_Replace searches the 1st parameter string for a matching pattern (2nd parameter) and replaces occurences with the 3rd parameter.

In the 3rd parameter, the '\1' says that any occurences of repeated ^ characters will be replaced by a single appearance of the same character within the parentheses. \1 actually means the 1st parentheses in the regular expression. You could use \2 if you had 2 sets of open-close paretheses if you need it and so on.

The + sign indicates that the pattern will look to match one or more repetitions of the previous character, which is the ^ within the parentheses. '\' is the escape character, I used it because the symbol ^ itself is a metacharacter.

Take a look at the docs if you find trouble understanding any of the concepts or if you're looking for a better overall grasp of the features.

Further information especifically on regexp_replace:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions147.htm

On POSIX Metacharacters used in Oracle Database Regular Expressions:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10471/adfns_regexp.htm#ADFNS1012

Glad I could be of help. ;)
1 person found this helpful
NicloeiW
Thanks every one,
I have lost almost all the touch in oracle, as i am involved in SAP project since pst 2 years :-((((
fsitja
Tell me about... I feel your pain too.

That's why I love these forums. :)
NicloeiW
haha, nothing i was n dedicated oracle resource for 6 years, doing application development and design,
now moved to business side with SAP Oracle Integration hence lost all touch in oracle,
haha
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 11 2010
Added on Jul 11 2010
7 comments
1,458 views