Solved

PL/SQL exclude a value from a string

Posted on 2009-05-12
3
532 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
[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 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 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 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

752 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