Oracle PL / SQL - String Match and Delete

Question

I have a function that compares values ​​from STR_Ac STR_B, the "dream" is that any matching characters between STR_Aand STR_Bwill be removed from STR_A.

For instance; STR_A = 'LTD'and STR_B = 'LIMITED'therefore the results will be null.

The order is important, so if you STR_A = 'LDT'and STR_B = 'LIMITED'the result will be 'T'.

Another example; STR_A = 'AUSTIN'and the STR_B = 'ADVERTISING'result will be'UT'

Note; the number of characters in STR_Awill always be less than or equal STR_B.

In addition, the symbol can be used only once, so if STR_A = 'LLTD'and STR_B = 'LIMITED', the result will be 'L'.

The function i has the following form:

create or replace FUNCTION CP_RDN_REMSTR(
  S1 VARCHAR2,
  S2 VARCHAR2)
 RETURN VARCHAR2
IS
 LEN INTEGER := NVL(LENGTH(S2),0);
 OUTSTR VARCHAR2(32767) := S1;
 POS INTEGER := 1;
 IND INTEGER := POS;
BEGIN
 FOR I IN 1..LEN
 LOOP
  POS := INSTR(SUBSTR(OUTSTR,POS),SUBSTR(S2,I,1));
  IF POS > 0 THEN
   OUTSTR := SUBSTR(OUTSTR,1,POS-IND)||SUBSTR(OUTSTR,POS+IND);
  END IF;
 END LOOP;
RETURN OUTSTR;
END;

;

SELECT CP_RDN_REMSTR('LTD','LIMITED') AS STR_A FROM DUAL
UNION ALL
SELECT CP_RDN_REMSTR('LDT','LIMITED') AS STR_A FROM DUAL
UNION ALL
SELECT CP_RDN_REMSTR('AUSTIN','ADVERTISING') AS STR_A FROM DUAL
UNION ALL
SELECT CP_RDN_REMSTR('ADP','ADVANCED') AS STR_A FROM DUAL

:

('LTD','LIMITED')        = NULL
('LDT','LIMITED')        = 'D'
('AUSTIN','ADVERTISING') = NULL
('ADP','ADVANCED')       = 'P'

:

('LTD','LIMITED')        = NULL
('LDT','LIMITED')        = 'T'
('AUSTIN','ADVERTISING') = 'UT'
('ADP','ADVANCED')       = 'P'

, .

Answer 1

, ( WITH), Oracle 11.2. Oracle , - PL/SQL.

LHS, . , , REGEXP_LIKE ( , NULL). , , ; , UNM ( "unmatched" ).

, LHS , RHS, . NULL ( , ).

, - , .

with
     inputs ( lhs, rhs ) as (
       select 'LTD'   ,'LIMITED'     from dual union all
       select 'LDT'   ,'LIMITED'     from dual union all
       select 'AUSTIN','ADVERTISING' from dual union all
       select 'ADP'   ,'ADVANCED'    from dual union all
       select 'ALPHA' , null         from dual union all
       select null    ,'BETA'        from dual
     ),
     r ( lvl, lhs, rhs, unm, pattrn, next_letter ) as (
       select  1, lhs, rhs, null, null, substr(lhs, 1, 1)
         from  inputs
       union all
       select  lvl + 1, lhs, rhs,
               unm    || case when regexp_like(rhs, pattrn || '.*' || next_letter)
                              then null else next_letter end,
               pattrn || case when regexp_like(rhs, pattrn || '.*' || next_letter)
                              then '.*' || next_letter end,
               substr(lhs, lvl + 1, 1)
         from  r
         where next_letter is not null
     )
     cycle lvl set cycle to 1 default 0
select lhs, rhs, unm
from   r
where  next_letter is null
;

( ):

LHS     RHS          UNM  
------  -----------  -----
        BETA                             
LTD     LIMITED                          
LDT     LIMITED      T                   
ADP     ADVANCED     P                   
ALPHA                ALPHA               
AUSTIN  ADVERTISING  UT   

. , . , r.

select   *
from     r
order by lhs, lvl
;

THAT.

. , , . (INSTR SUBSTR), , . , , , , ( ); RHS , .

with
     inputs ( lhs, rhs ) as (
       select 'LTD'   ,'LIMITED'     from dual union all
       select 'LDT'   ,'LIMITED'     from dual union all
       select 'AUSTIN','ADVERTISING' from dual union all
       select 'ADP'   ,'ADVANCED'    from dual union all
       select 'ALPHA' , null         from dual union all
       select null    ,'BETA'        from dual
     ),
     r ( lhs, rhs, next_letter, unm, new_lhs, new_rhs ) as (
       select  lhs, rhs, substr(lhs, 1, 1), null, lhs, rhs
         from  inputs
       union all
       select  lhs, rhs, substr(new_lhs, 2, 1),
               unm || case when nvl(instr(new_rhs, next_letter), 0) = 0 
                           then next_letter end,
               substr(new_lhs, 2),
               substr(new_rhs, nvl(instr(new_rhs, next_letter), 0) + 1) 
         from  r
         where next_letter is not null
    )
    cycle new_lhs set cycle to 1 default 0
select lhs, rhs, unm
from   r
where  next_letter is null
;

Answer 2

, S2 S1, S1.

, , :

CREATE OR REPLACE FUNCTION CP_RDN_REMSTR (S1 VARCHAR2, S2 VARCHAR2) RETURN VARCHAR2
IS
   LEN      INTEGER := NVL (LENGTH (S2), 0);
   OUTSTR   VARCHAR2 (32767) := S1;
   POS      INTEGER := 1;
   IND      INTEGER := POS;
BEGIN
   FOR I IN 1 .. LEN
   LOOP
      POS := 0;
      POS := INSTR (SUBSTR (OUTSTR, POS), SUBSTR (S2, I, 1));

      IF POS = 1 THEN
         OUTSTR := SUBSTR (OUTSTR, 1, POS - IND) || SUBSTR (OUTSTR, POS + IND);
      END IF;
   END LOOP;

   RETURN OUTSTR;
END CP_RDN_REMSTR;

:

('LTD','LIMITED')        = NULL 
('LDT','LIMITED')        = 'T'
('AUSTIN','ADVERTISING') = 'USTIN' 
('ADP','ADVANCED')       = 'P'