Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PL/SQL exclude a value from a string

Posted on 2009-05-12
3
Medium Priority
?
558 Views
Last Modified: 2013-12-07
I have created a function that returns
for example, 'JOHN DOE / MIGRATED / JOHN DOE'
The function contains a cursor that returns signatures.   Each time it loops through the cursor, it concatenates the signature and '/' as a delimiter to separate the signatures.

In this example, there are three signatures.  If there are duplicate signatures  or a signature with the value 'MIGRATED',  I would like to exclude for example the duplicate signature, 'MIGRATED' and the slashes '/' and return only  one 'JOHN DOE'.

Is there a way to do this?
CURSOR get_sig IS
   SELECT
     signer_name
     
   FROM
     ia,
     ib,
     ic,
     is
  WHERE
     ia.itl_id             = 
 
ib.itl_id 
     AND ia.itl_ac_rting_id        = 
 
ic.itl_ac_rting_id
     AND ia.itl_ac_rting_step_id   = 
 
ic.itl_ac_rting_step_id
     AND ic.itl_sig_id        = is.itl_sig_id
     AND ic.t_step = 'Y'
     AND ic.status <> 'DISAPPROVED'
     AND ib.itl_id =  p_accred_id;
 
 
 BEGIN
   FOR final_sig_rec IN get_final_acc_signature
   LOOP
     cnt := cnt + 1;
     if cnt > 1 then
       vsig := vsig || ' / ' || final_sig_rec.signer_name;
     else
       vsig := final_sig_rec.signer_name;  
     end if;   
   END LOOP;
   return vsig;
Exception When Others then
   RETURN '';

Open in new window

0
Comment
Question by:cookiejar
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 24371515
how about something like this...


SELECT   DISTINCT TRIM(COLUMN_VALUE) signature
  FROM   table(str2tbl('JOHN DOE / MIGRATED / JOHN DOE', '/'))
 WHERE   TRIM(COLUMN_VALUE) != 'MIGRATED'


where str2tbl is the following function...
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);
 
            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;
 
        RETURN;
    END str2tbl;

Open in new window

0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 24376114
or before line 30 you can add a condition like

if instr(vsig || ' / ', final_sig_rec.signer_name || ' / ')  = 0 then
 -- your line 30-34 here
else
 -- nothing here, it is duplicate, skip this record
end if;
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

885 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