Home » SQL & PL/SQL » SQL & PL/SQL » How to remove carriage return?
How to remove carriage return? [message #221977] Thu, 01 March 2007 06:28 Go to next message
arsheshadri
Messages: 12
Registered: January 2006
Junior Member
Hi,

I have a strange problem in my below query.

SQL>Select comment_text from Comments;

ROWNUM COMMENT_TEXT
---------- --------------------------------------------------
1 scope.
Except for the above in the auditors


Somehow a carriage is entered and the query displays results as above.
i.e "scope." in first line and
"Except for the above in the auditors" in second line.

Is there any way in which, I can fetch above results in one line as below?

SQL>Select comment_text from Comments;

ROWNUM COMMENT_TEXT
---------- --------------------------------------------------
1 scope.Except for the above in the auditors

Thanks & Regards
Sheshadri
Re: How to remove carriage return? [message #221993 is a reply to message #221977] Thu, 01 March 2007 07:26 Go to previous messageGo to next message
Adrian Billington
Messages: 139
Registered: December 2002
Senior Member
SQL> with comments as (
  2          select 'Scope.' || chr(10) || 'Except for the above in the auditors.' as comment_text
  3          from   dual
  4          )
  5  select rownum, comment_text
  6  from   comments;

    ROWNUM COMMENT_TEXT
---------- --------------------------------------------
         1 Scope.
           Except for the above in the auditors.


SQL>
SQL> with comments as (
  2          select 'Scope.' || chr(10) || 'Except for the above in the auditors.' as comment_text
  3          from   dual
  4          )
  5  select rownum, replace(comment_text,chr(10)) as comment_text
  6  from   comments;

    ROWNUM COMMENT_TEXT
---------- -------------------------------------------
         1 Scope.Except for the above in the auditors.



Or for 10g...

SQL>
SQL> with comments as (
  2          select 'Scope.' || chr(10) || 'Except for the above in the auditors.' as comment_text
  3          from   dual
  4          )
  5  select rownum, regexp_replace(comment_text,'[[:cntrl:]]') as comment_text
  6  from   comments;

    ROWNUM COMMENT_TEXT
---------- -------------------------------------------
         1 Scope.Except for the above in the auditors.


Regards
Re: How to remove carriage return? [message #222004 is a reply to message #221977] Thu, 01 March 2007 07:49 Go to previous message
arsheshadri
Messages: 12
Registered: January 2006
Junior Member
Thanks for the reply. My problem is solved Cool
Previous Topic: insert multiple rows
Next Topic: Is %ROWCOUNT compatible with 10g?
Goto Forum:
  


Current Time: Sat Dec 03 10:20:24 CST 2016

Total time taken to generate the page: 0.24580 seconds