asked on

Newline in Oralce Varchar2 field

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

yourcolumn = 'first line' || chr(10) || 'second line'

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toooki

ASKER

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

Sean Stuber

that may be your tool ignoring the special characters.

use sql*plus

slightwv (䄆 Netminder)

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

Sean Stuber

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

toooki

ASKER

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