We help IT Professionals succeed at work.

Newline in Oralce Varchar2 field

toooki
toooki asked
on
Is it possible to insert a newline character or so in a Oracle varchar2 field (11gR2)?

I am writing text values to a log table's varchar2 column. If certain part of the column contents were newline separated then the table contents could be more readable. I cannot change it to CLOB datatype.

Is there any alternate solution for the above if the newline character cannot be added to a varchar2 field?
Thanks.
I wanted to o/p: select t1 from MyLog as:

Start time: 05-DEC-2011 18:16:00 End time 04-DEC-2011 19:16:00
Start time: 05-DEC-2011 18:16:00 End time 05-DEC-2011 19:16:00
Start time: 05-DEC-2011 18:16:00 End time 06-DEC-2011 19:16:00


As opposed to current: select t1 from MyLog as:
Start time: 05-DEC-2011 18:16:00 End time 04-DEC-2011 19:16:00 Start time: 05-DEC-2011 18:16:00 End time 05-DEC-2011 19:16:00 Start time: 05-DEC-2011 18:16:00 End time 06-DEC-2011 19:16:00

Open in new window

Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Just  use a chr(13) || chr(10)


insert into log_table('line1' || chr(13) || chr(10) || 'line2'  || chr(13) || chr(10) || 'line3 );
Most Valuable Expert 2011
Top Expert 2012

Commented:
yourcolumn = 'first line' || chr(10) || 'second line'
Most Valuable Expert 2011
Top Expert 2012
Commented:
chr(13)  - this is a carriage return

chr(10) - this is a new line

Author

Commented:
Thank you. I tried that but it is not showing the newline character when I do select * from the table. I have attached the screen.
create table t2 (f1 varchar2(60));
insert into t2 values ('line1' || chr(13) || chr(10) || 'line2');
select * from t2;

Open in new window

screen
Most Valuable Expert 2011
Top Expert 2012

Commented:
that may be your tool ignoring the special characters.

use sql*plus
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
works in sqlplus.
SQL> create table t2 (f1 varchar2(60));

Table created.

SQL> insert into t2 values ('line1' || chr(13) || chr(10) || 'line2');

1 row created.

SQL> select * from t2;
line1
line2

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
using your real table  try this  in sql plus or some other tool that will observe line feeds


SELECT REPLACE(t1, ' Start time', CHR(10) || 'Start time')
  FROM mylog

Author

Commented:
Thank you. That is right. It was because of the editor.
I see it is working.
Thank you.