Solved

How to use regexp_replace to replace mutiple occurance of substring?

Posted on 2013-06-03
6
2,206 Views
Last Modified: 2013-06-03
hi,
would you know if i can use regexp_replace to replace multiple occurances of a substring with in a string, with a single occurance of the string?
i have a string like so..
'XP78380;XT42564;ZX03942;XP78380;XT42564;'
i want to remove duplicate occurance of 'XP78380' and 'XT42564' and have only one occurance.
i wrote a small pl/sql block, but want to check if this can be done with a single regexp_replace?
0
Comment
Question by:Rao_S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 39216340
Main code copied from:
http://volder-notes.blogspot.com/2007/10/removing-duplicate-elements-from-string.html

Not sure how efficient it is over the good old str2tbl and listagg to put it all back together but you can test both and see.

Try this:
WITH t AS (SELECT 'XP78380;XT42564;ZX03942;XP78380;XT42564;' str FROM dual)
    --
select str, rtrim(str_new,',') new_str from t
model
dimension by (0 dim)
measures(str, str||',' str_new)
rules iterate(100) until (str_new[0] = previous(str_new[0]))
(str_new[0]=regexp_replace(str_new[0],'(^|;)([^;]+;)(.*?;)?\2+','\1\2\3'));

Open in new window

0
 

Author Comment

by:Rao_S
ID: 39216377
wow! thank you, i need to spend some time and understand how this works, my string is not a constant, at one time it can have 10 elements in it separated by a ';' at another time it might have just 2 elements in it, regardless of the number of elements in it, i need to parse it and remove dups...
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 334 total points
ID: 39216579
Assuming the output should be sorted alphabetically

select listagg(str,';') within group (order by str) ||';' from
(SELECT     DISTINCT REGEXP_SUBSTR('XP78380;XT42564;ZX03942;XP78380;XT42564;', '[^;]+', 1, LEVEL) str
      FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('XP78380;XT42564;ZX03942;XP78380;XT42564;', '[^;]+'));


If they won't be sorted alphabetically but you need to preserve the initial order  then


SELECT LISTAGG(str, ';') WITHIN GROUP (ORDER BY lvl) || ';'
  FROM (SELECT str, lvl, ROW_NUMBER() OVER (PARTITION BY str ORDER BY lvl) rn
          FROM (SELECT     REGEXP_SUBSTR('XP78380;XT42564;ZX03942;XP78380;XT42564;', '[^;]+', 1, LEVEL)
                               str,
                           LEVEL lvl
                      FROM DUAL
                CONNECT BY LEVEL <=
                               REGEXP_COUNT('XP78380;XT42564;ZX03942;XP78380;XT42564;', '[^;]+')))
 WHERE rn = 1;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 334 total points
ID: 39216606
and another version,  no aggregation or sorting needed, simply find the dups and remove them


SELECT CASE
           WHEN INSTR(str, 'XT42564', 1, 2) > 0
           THEN
               REGEXP_REPLACE(str, 'XT42564' || ';', NULL, INSTR(str, 'XT42564', 1, 2), 0)
           ELSE
               str
       END
           str
  FROM (SELECT CASE
                   WHEN INSTR(str, 'XP78380', 1, 2) > 0
                   THEN
                       REGEXP_REPLACE(str, 'XP78380' || ';', NULL, INSTR(str, 'XP78380', 1, 2), 0)
                   ELSE
                       str
               END
                   str
          FROM (SELECT 'XP78380;XT42564;ZX03942;XP78380;XT42564;' str FROM DUAL))
0
 

Author Comment

by:Rao_S
ID: 39216620
wow!!!!!!
this is wonderful too! i knew regexp was very powerful, but just dint know how to use it!
thank you very much, i love the first select!! also i have never used a connect by level before..
0
 

Author Closing Comment

by:Rao_S
ID: 39216921
wonderful solutions!
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question