Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PL/SQL exclude a value from a string

Posted on 2009-05-12
3
Medium Priority
?
547 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 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 59

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

718 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