6 Replies Latest reply: Jul 16, 2010 12:10 PM by MichaelS RSS

    Removing carriage return at the start and end of the line

    780639
      Hey,

      Have a small doubt of how to remove leading and trailing carriage returns in oracle..

      Ex :
      Say string is :

      ( '
      abc
      ')

      Should get 'abc'.

      Thanks
        • 1. Re: Removing carriage return at the start and end of the line
          LKBrwn_DBA
          Try the REPLACE() function.

          HINT: Carriage return = CHR(10)
          :p
          • 2. Re: Removing carriage return at the start and end of the line
            780639
            Did not work for me..trying to use regexp to replace leading and trailing carriage returns..
            replace will replace even carriage returns in between as well.

            Carriage return is chr(13) || chr(10)
            • 3. Re: Removing carriage return at the start and end of the line
              John Spencer
              As a regexp_replace, something like:
              SQL> SELECT 'a'||x||'a' oldx,
                2         'b'||regexp_replace(x, '^'||(chr(13)||chr(10))||'|'||(chr(13)||chr(10))||'$')||'b' newx
                3  FROM (
                4  SELECT chr(13)||chr(10)||'This is before internal cr/lf'||
                5         chr(13)||chr(10)||'This is after'||chr(13)||chr(10) x
                6  FROM dual);
               
              OLDX                           NEWX
              ------------------------------ ------------------------------
              a                              bThis is before internal cr/lf
              This is before internal cr/lf  This is afterb
              This is after
              a
              You could also do it with a nested LTRIM/Rtrim to preserve internal newlines:
              SQL> SELECT 'a'||x||'a' oldx,
                2         'b'||RTRIM(LTRIM(x, chr(13)||chr(10)), chr(13)||chr(10))||'b'
                3  FROM (
                4  SELECT chr(13)||chr(10)||'This is before internal cr/lf'||
                5         chr(13)||chr(10)||'This is after'||chr(13)||chr(10) x
                6  FROM dual)x
               
              OLDX                            NEWX
              ------------------------------- -------------------------------
              a                               bThis is before internal cr/lf
              This is before internal cr/lf   This is afterb
              This is after
              a
              John
              • 4. Re: Removing carriage return at the start and end of the line
                MichaelS
                Or
                SQL> with t as
                (
                   select '
                a
                bc
                ' s from dual
                )
                --
                --
                select trim(chr(10) from trim(chr(13) from trim(chr(10) from s))) s from t
                /
                S   
                ----
                a   
                bc  
                                   
                Edited by: michaels2 on Jul 16, 2010 7:09 PM
                • 5. Re: Removing carriage return at the start and end of the line
                  John Spencer
                  Not with chr(13)||chr(10) as the newline pair. :-)
                  SQL> SELECT DUMP(trim(chr(13) from trim(chr(10) from x)))
                    2  FROM (
                    3  SELECT chr(13)||chr(10)||'This is before internal cr/lf'||
                    4         chr(13)||chr(10)||'This is after'||chr(13)||chr(10) x
                    5  FROM dual);
                  DUMP(TRIM(CHR(13)FROMTRIM(CHR(10)FROMX)))
                  -------------------------------------------------
                  Typ=1 Len=45: 10,84,104,105,115,32,<snip>,101,114
                  You are still left with a leading chr(10), you would need another TRIM(chr(10)).

                  John
                  • 6. Re: Removing carriage return at the start and end of the line
                    MichaelS
                    You are right ;)

                    Modified above query.