Home » SQL & PL/SQL » SQL & PL/SQL » how to delim records in sqlplus
how to delim records in sqlplus [message #219197] Tue, 13 February 2007 09:48 Go to next message
libanori
Messages: 11
Registered: February 2007
Location: ireland
Junior Member

Hi,

Is there any way to delim records in sqlplus?
If I ran a query in sqlplus and I want to add pipe or comma at the end of each record, what is the best way?
thanks.
Re: how to delim records in sqlplus [message #219200 is a reply to message #219197] Tue, 13 February 2007 09:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
scott@9i > set colsep '|';
scott@9i > select * from dept;

    DEPTNO|DNAME         |LOC
----------|--------------|-------------
        10|ACCOUNTING    |NEW YORK
        20|RESEARCH      |DALLAS
        30|SALES         |CHICAGO
        40|OPERATIONS    |BOSTON
Re: how to delim records in sqlplus [message #219201 is a reply to message #219197] Tue, 13 February 2007 09:54 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
The most straight forward way would simply be to concatenate your delim symbol at the end of your select ..

Select emp_no,emp_name,'delim_symbol' from emp;


Not sure if that's what you are after or not
Re: how to delim records in sqlplus [message #219214 is a reply to message #219201] Tue, 13 February 2007 10:24 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
...or you might be able to just use the concatenation operator

select
emp_no || '|' ||
emp_name || '|'
from emp;

123|Smith|
456|Jones|
Re: how to delim records in sqlplus [message #219226 is a reply to message #219197] Tue, 13 February 2007 11:02 Go to previous messageGo to next message
libanori
Messages: 11
Registered: February 2007
Location: ireland
Junior Member

Thanks all you guys for your quick response.All solutions worked fine for me.
Re: how to delim records in sqlplus [message #219240 is a reply to message #219197] Tue, 13 February 2007 11:57 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
No no no guys...use colsep as Mahesh indicated if that is what you are after, or use recsep and recsepchar if that is what you need. In general, use the built-in functionality, it is easier to use and maaintain, and it generally performs better.
Previous Topic: Query do not show the time 00:00:00
Next Topic: case-sensitive
Goto Forum:
  


Current Time: Thu Dec 08 18:42:25 CST 2016

Total time taken to generate the page: 0.09480 seconds