10 Replies Latest reply: Mar 19, 2008 8:58 PM by pl/sql novice RSS

    Remove a Carriage return

    205837
      I need to remove carriage returns from the beginning of the field I am retrieving.

      I have tried replace(chr(13), <field>, '') I even tried rtrim(<field>,chr(13))

      Anyone have any ideas?
        • 1. Re: Remove a Carriage return
          263831
          Ensure that it is a carriage return...may be a line feed which is chr(10).
          • 2. Re: Remove a Carriage return
            4174
            I think you have the use of replace backwards. Try replace(<field>, chr(13)).
            • 3. Re: Remove a Carriage return
              4174
              You can verify whether it's chr(10) ot chr(13) or something else by using:

              select ascii(substr(<field>,1,1)) from <table>
              • 4. Re: Remove a Carriage return
                205837
                I did the ascii command and it came back with a 13. When I tried a replace. It only replaced one of several carriage returns with the the value but it also wiped out the rest of the text in the field. When I tried a ltrim, it returned a data field of null. Vey strange. Interestingly, the replace does not appear to be working with the chr(13)

                I have tried:
                replace(<field>,chr(13), 'a')
                replace(<field>,chr(13), '')
                replace(<field>,chr(13))
                replace(<field>,chr(10), 'a')

                Other Ideas.
                • 5. Re: Remove a Carriage return
                  4174
                  That's very strange. What version of the database are you using and what tool to run the query? It works on my database using SQL*Plus:

                  SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 19 17:13:03 2004

                  Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


                  Connected to:
                  Oracle9i Release 9.2.0.4.0 - 64bit Production
                  JServer Release 9.2.0.4.0 - Production

                  SQL> create table altest(c1 varchar2(10));

                  Table created.

                  SQL> insert into altest values('abc');

                  1 row created.

                  SQL> insert into altest values('bab');

                  1 row created.

                  SQL> insert into altest values('bcd');

                  1 row created.

                  SQL> commit;

                  Commit complete.

                  SQL> select replace(c1,'a') from altest;

                  REPLACE(C1
                  ----------
                  bc
                  bb
                  bcd

                  SQL> insert into altest values(chr(13)||'abc');

                  1 row created.

                  SQL> insert into altest values('a'||chr(13)||'a');

                  1 row created.

                  SQL> commit;

                  Commit complete.

                  SQL> select * from altest;

                  C1
                  ----------
                  abc
                  bab
                  bcd

                  abc
                  a
                  a

                  SQL> select replace(c1,chr(13)) from altest;

                  REPLACE(C1
                  ----------
                  abc
                  bab
                  bcd
                  abc
                  aa
                  • 6. Re: Remove a Carriage return
                    205837
                    I am using Toad and Oracle 9.2.0.? on Win2000. I have gone through all of the same commands.
                    • 7. Re: Remove a Carriage return
                      2889
                      show us the result of your query :

                      select dump(your_field), your_field from your_table ;

                      so we might find out which character is giving you headache.
                      • 8. Re: Remove a Carriage return
                        205837
                        Excellent command. I see the line is starting with a carriage return then a line feed

                        Typ=1 Len=223: 13,10,87,65,78,32,117,112,103,114,97,100,101,115,32,119,105,108,108,32,97,108,108,111,119,32,116,104,101,32,115,100,32,66,117,100,103,101,116,32,110,101,116,119,111,114,107,115,32,105,110,32,97,32,99,111,110,115,105,115,116,101,110,116,32,109,97,110,110,101,114,46,13,10,13,10,84,104,101,32,101,120,105,115,116,105,110,103,32,66,117,100,103,101,116,32,87,65,78,32,110,101,116,119,111,114,107,115,32,119,105,108,108,32,98,101,32,109,105,103,114,97,116,101,100,32,102,114,111,109,32,76,105,115,108,101,32,73,108,108,105,110,111,105,115,32,116,111,32,68,101,110,118,101,114,46
                        • 9. Re: Remove a Carriage return
                          205837
                          Problem solved. I did a :

                          select replace(replace(<field>,chr(10),' '),chr(13),' ')
                          FROM table

                          Works Great!!

                          Thanks for everyones help,
                          John
                          • 10. Re: Remove a Carriage return
                            pl/sql novice
                            For your info, I use the following in a pl/sql function to clean these invisible characters:

                            tmpbuffer := regexp_replace(tmpbuffer,'([^[:graph:]|^[:blank:]])','');

                            Have a nice day.