OraQA

The following SQL pattern can be used to do character based data manipulation
by utilizing the XQuery functionalities.

COLUMN old_str  FORMAT  A28
COLUMN ordered_no_dup_chars FORMAT A23
COLUMN Reversed_chars FORMAT  A23



--------------------SQL Query-------------------


WITH data AS
(SELECT '3332221118888'   AS  old_str  FROM dual
  UNION ALL
  SELECT 'CCCBBBAAA'          FROM dual 
   UNION ALL
  SELECT 'XXXYYY33332222'     FROM dual 
)          
SELECT old_str, ordered_no_dup_chars,
       XMLQUERY('fn:codepoints-to-string(fn:reverse(fn:string-to-codepoints($str)))'
                 PASSING XML.ordered_no_dup_chars AS "str" 
                 RETURNING CONTENT) AS Reversed_chars
FROM data d,                                
XMLTable('fn:codepoints-to-string(for $i in fn:distinct-values(fn:string-to-codepoints($str)) 
           order by $i ascending empty least return $i)'
           PASSING d.old_str  AS "str"
           COLUMNS  ordered_no_dup_chars varchar2(4000) PATH '.'
) XML;






OLD_STR                      ORDERED_NO_DUP_CHARS    REVERSED_CHARS
---------------------------- ----------------------- -----------------------
3332221118888                1238                    8321
CCCBBBAAA                    ABC                     CBA
XXXYYY33332222               23XY                    YX32

SQL>

Filed under General, PL/SQL, SQL, XML. Follow any responses to this entry through this RSS 2.0 feed.