7 Replies Latest reply: Oct 9, 2013 3:59 AM by Pablolee RSS

    Replace carriage return or new line with space in table

    815519

      Hi

       

      i want to replace carriage return or new line with space in my one of the column of the database.

      For this , i am using this query :

       

      select replace(replace(Comments,chr(10),' '),chr(13),' ') FROM Comments_Master

       

      Comments_Master is table name and Comments is column on which contains carriage return or new line.

      But this query is not working as expected.

       

      Once i run this query and again execute the query " select * FROM Comments_Master " , carriage return and new line still exist.

      Please let me know what is problem with this query ?

        • 1. Re: Replace carriage return or new line with space in table
          Pablolee

          Without seeing your data, I'd guess that waht you think are characters 13 and/or 10 are different. Perform a Substring to get one of those characters and then wrap ascii around it to see what character it actually is.

          • 2. Re: Replace carriage return or new line with space in table
            815519

            I have checked these data and these are new line/carriage return only.

            These new line and carriage return has come into table through front end of my application and now i want to cleanse my data.

            As old data with new line/carriage return is creating some issues. For new data , i am not allowing my user to enter new line/carriage return through front end.

             

            So want to execute query for all data of that specific column which contains new line/carriage return will be replaced by single space.

            • 3. Re: Replace carriage return or new line with space in table
              adi999

              You are writing the data back aren't you? This example shows that your sql should work. I thinnk the query you've just described is the update one below.

               

              create table blah ( text varchar2(100));

               

              insert into blah values ('This is multi

              line text');

              select * from blah;

              select replace(replace(text,chr(10),' '),chr(13),' ') from blah;

              update blah set text = replace(replace(text,chr(10),' '),chr(13),' ');

              select * from blah;

              • 4. Re: Replace carriage return or new line with space in table
                Pablolee

                815519 wrote:

                 

                I have checked these data and these are new line/carriage return only..

                Can you post evidence of this please.

                815519 wrote:

                These new line and carriage return has come into table through front end of my application and now i want to cleanse my data.

                Is that what you are basing your assumption on that they are newline and cr characters, or did you check the data in the actual table? Please post evidence.

                815519 wrote:

                So want to execute query for all data of that specific column which contains new line/carriage return will be replaced by single space.

                I understand what it is that you want to do, my point is that I'm betting that the characters that you are trying to replace are not Chr(10) /Chr(13) (Although, I could be wrong and there could be some other reason)

                • 5. Re: Replace carriage return or new line with space in table
                  BluShadow

                  I think adi999 has perhaps picked up on the answer.

                   

                  Are you actually "updating" the table data to replace the newlines?

                  From what you've shown you are just querying the data, not updating it, so when you do a "select * from..." of course it will still be the same as it always was.

                  • 6. Re: Replace carriage return or new line with space in table
                    Ramin Hashimzadeh

                    Remember SELECT only selects your data, not changing it

                    if you want to change it use UPDATE query to changes rows in your table, from your question answer will be below :

                     

                    update Comments_Master

                    set Comments = replace(replace(Comments,chr(10),' '),chr(13),' ');

                    commit;

                     

                    select Comments FROM Comments_Master


                    ----

                    Ramin Hashimzade

                    • 7. Re: Replace carriage return or new line with space in table
                      Pablolee

                      I completely missed that last sentence in the OP! More coffee!