OraQA

The following two SQL patterns can be used to find missing characters based on the alphanumeric character list.

variable input varchar2(80)
exec :input  := '012345abcdeABCDE';

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

SELECT translate(regexp_replace(XMLAgg(XMLElement(X,ch)
ORDER BY ch),'<X>|</X><X>|</X>'),chr(0)||:input, chr(0)) as missing_chars
FROM (SELECT chr(ascii('0')+LEVEL-1) ch
      FROM dual CONNECT BY LEVEL <=100)
WHERE CASE WHEN ascii(ch)  BETWEEN ascii('0') AND ascii('9') OR
                ascii(ch)  BETWEEN ascii('a') AND ascii('z') OR
                ascii(ch)  BETWEEN ascii('A') AND ascii('Z')
           THEN 1 END =1;

MISSING_CHARS
--------------------------------------------------
6789FGHIJKLMNOPQRSTUVWXYZfghijklmnopqrstuvwxyz

—————– Case Insensitive SQL Solution ——————

SELECT missing_chars
FROM
(WITH ALPNUM AS
(SELECT
 (SELECT regexp_replace(XMLAgg(XMLElement(X,ch)
                        ORDER BY ch),'<X>|</X><X>|</X>')
  FROM (SELECT chr(ascii('0')+LEVEL-1) ch
          FROM dual CONNECT BY LEVEL <=100)
  WHERE CASE WHEN ascii(ch) BETWEEN ascii('0') AND ascii('9') OR
                  ascii(ch) BETWEEN ascii('A') AND ascii('Z')
             THEN 1 END =1) alpnum
  FROM DUAL
),
INPUT AS
(SELECT
 (SELECT regexp_replace(XMLAgg(XMLElement(X,upper(ch))
                        ORDER BY ch ),'<X>|</X><X>|</X>')
  FROM
  (SELECT doc.extract('/X/text()').getStringVal() ch
   FROM
   TABLE(xmlSequence(extract(XMLType('<DOC>'||
    REGEXP_REPLACE(:input,'(.)','<X>\1</X>')||'</DOC>'),'/DOC/X'))) doc
  )
 ) str
 FROM DUAL
)
SELECT translate((SELECT alpnum FROM ALPNUM), chr(0)||(SELECT str from input), chr(0)) as missing_chars
FROM DUAL
);

MISSING_CHARS
------------------------------------
6789FGHIJKLMNOPQRSTUVWXYZ

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