Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

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

0
cookiejar
Asked:
cookiejar
2 Solutions
 
sdstuberCommented:
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
 
HainKurtSr. 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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now