Friday, 29 March 2013

To Avoide The Special Character chr(13) and chr(10)

/* Formatted on 3/29/2013 4:09:14 PM (QP5 v5.114.809.3010) */
SELECT   'a' || CHR (10) || 'b' FROM DUAL;

/* Formatted on 3/29/2013 4:09:11 PM (QP5 v5.114.809.3010) */
SELECT      'The total count is:'
         || CHR (10)
         || 'Registration: 1111'
         || CHR (10)
         || 'Fees paid: 2222'
         || CHR (10)
         || 'Admission Done: 3333'
         || CHR (10)
         || 'Total:4000'
  FROM   DUAL


You can use REGEXP_REPLACE to add a newline after every 3 characters
:-

/* Formatted on 3/29/2013 4:09:04 PM (QP5 v5.114.809.3010) */
SELECT   loc, REGEXP_REPLACE (loc, '(.{3})', '\1' || CHR (10)) AS loc3
  FROM   scott.dept



/* Formatted on 3/29/2013 4:08:54 PM (QP5 v5.114.809.3010) */
DECLARE
   v_desc_en_4k VARCHAR2 (1000)
         :=    CHR (10)
            || 'A = Add account'
            || CHR (13)
            || CHR (10)
            || 'C = Change account'
            || CHR (13)
            || CHR (10)
            || 'D = Delete account'
            || CHR (13)
            || CHR (10) ;
BEGIN
   DBMS_OUTPUT.put_line ('----before trim----');
   DBMS_OUTPUT.put_line (v_desc_en_4k);
   DBMS_OUTPUT.put_line ('-------------');
   DBMS_OUTPUT.put_line ('-----after trim----');
   v_desc_en_4k := TRIM (CHR ( 13 ) FROM TRIM (CHR ( 10 ) FROM v_desc_en_4k));
   DBMS_OUTPUT.put_line (v_desc_en_4k);
   DBMS_OUTPUT.put_line ('-------------');
END;

No comments:

Post a Comment