Solved

PL/SQL exclude a value from a string

Posted on 2009-05-12
3
519 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 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
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 51

Assisted Solution

by:HainKurt
HainKurt earned 250 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now