OraQA

The following SQL pattern can be used to simulate Minus/Intersect/Union All/Union set operation on characters:

variable str_1  varchar2(80)
variable str_2  varchar2(80)
exec :str_1  := 'AABBCC';
exec :str_2  := 'BBCCDD';

COLUMN INPUT_1 FORMAT A10
COLUMN INPUT_2 FORMAT A10
COLUMN MINUS_ FORMAT A10
COLUMN INTERSECT_ FORMAT A12
COLUMN UNION_ALL_ FORMAT A20
COLUMN UNION_ FORMAT A10

———————————SQL Solution—————————-

SELECT str_1 AS input_1, str_2 AS input_2, union_str as UNION_,
concat(str_1, str_2) UNION_ALL_,
translate(union_str, chr(0)||str_2, chr(0)) MINUS_,
translate(union_str,
                 chr(0)||translate(str_1, chr(0)||str_2, chr(0))
                           ||translate(str_2, chr(0)||str_1, chr(0)), chr(0)) AS INTERSECT_
FROM
(SELECT :str_1 as str_1, :str_2 as str_2,
        regexp_replace(XMLAgg(XMLElement(X, ch) ORDER BY ch),'<X>|</X><X>|</X>') union_str
 FROM
  (SELECT DISTINCT doc.extract('/X/text()').getStringVal() ch
   FROM
   TABLE(xmlSequence(extract(XMLType('<DOC>'||REGEXP_REPLACE(
       concat(:str_1, :str_2),'(.)','<X>\1</X>')||'</DOC>'),'/DOC/X'))) doc
  )
 );
INPUT_1    INPUT_2    UNION_     UNION_ALL_           MINUS_     INTERSECT_
----------    ----------     ----------      --------------------    ---------- ------------
AABBCC     BBCCDD     ABCD       AABBCCBBCCDD         A          BC

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.