OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to do character based data manipulation in SQL and XQuery

April 2nd, 2012 By Frank Zhou

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>

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question