Re: Does CHR(10) add an extra newline?

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Thu, 22 Nov 2007 04:32:22 -0800 (PST)
Message-ID: <c3809ca3-932a-4a72-9c11-76faa51b86f4@n20g2000hsh.googlegroups.com>

On 21 nov, 20:53, Brian Tkatch <N/A> wrote:
> On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr..._at_cox.net"
>
>
>
> <fitzjarr..._at_cox.net> wrote:
> >On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> >wrote:
> >> Brian Tkatch wrote:
> >> > While writing a query to do some editting i wanted to use CHR(10) to
> >> > have a query do some formatting. It seems that whenever it is used, it
> >> > adds an extra newline at the end of the line.
>
> >> > SQL> SELECT RowNum A, CHR(10) || RowNum B FROM (SELECT * FROM Dual
> >> > CONNECT BY LEVEL < 4)
>
> >> No - your display simply is not wide enough.
> >> Why don't you check out all possible settings in SQL*Plus?
> >> the one you want is SET LINES[ize]
>
> >> --
> >> Regards,
> >> Frank van Bortel
>
> >> Top-posting is one way to shut me up...
>
> >I return the same results that you posted, using 10.2.0.3.0 on AIX:
>
> >SQL> SELECT RowNum A, CHR(10) || RowNum B FROM (SELECT * FROM Dual
> > 2 CONNECT BY LEVEL < 4);
>
> > A B
> >-- -----------------------------------------
> > 1
> > 1
>
> > 2
> > 2
>
> > 3
> > 3
>
> > no matter how long my linesize. Also the method of introducing the
> >chr(10) into the text matters not:
>
> >SQL> variable lf varchar2
> >SQL> exec :lf := chr(10);
>
> >PL/SQL procedure successfully completed.
>
> >SQL> select rownum a, :lf||rownum b
> > 2 from dual
> > 3 connect by level < 4;
>
> > A B
>
> Thank you. I came up with it originally in a CASE statement, to create
> CREATE VIEW statements. I worked around it by opening Word, and doing
> a replace on ",^p^p". Though, i am quite curious why this is
> happening.
>
> And thanx for the example in PL/SQL.
>
> B.

You normally would not create views "on the fly"... Apart from that - there's no need for carriage returns in DDL, but if you really want them, just embed the carriage return in the statement:

SQL> select 'hello
  2 world' from dual;

'HELLOWORLD'

---

hello
 world

And yes- there's an extra space before Hello on line 2. Received on Thu Nov 22 2007 - 06:32:22 CST