PL/SQL exclude a value from a string

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

cookiejarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
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
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.