How to include double quotes around string result in sqlplus?

Question

We are using SQL*Plus to export data to a csv file. We have both decimal and text columns, and each field is delimited by comma (,) but some text columns also contain commas. While importing data into some other DB we are seeing that these commas in the text are treated as column separators. Can anyone tell me how I can overcome this?

set tab off
SET head OFF
SET feedback OFF
SET pagesize 0
SET linesize 3000;
SET colsep ,
set trimspool on
set trimout on
set trims on
set null ""
set rowprefetch 2
set feedback off
set arraysize 1000
set PAGESIZE 50000
set STATEMENTCACHE 20
set numwidth 15
column coulmnName format 999999999.99
column coulmnName format 999999999.99
column coulmnName format 999999999.99
column coulmnName format 999999999.99

Solution 1

Just concatenate a double quote character to the start and end of columns with a string data type:

SELECT '"' || stringColumnA || '"' AS stringColumnA,
       numberColumnB,
       '"' || stringColumnC || '"' AS stringColumnC
       -- ...
FROM   table_name;

If your column already contains double quotes then escape them by doubling them up:

SELECT '"' || REPLACE( stringColumnA, '"', '""' ) || '"' AS stringColumnA,
       numberColumnB,
       '"' || REPLACE( stringColumnC, '"', '""' ) || '"' AS stringColumnC
       -- ...
FROM   table_name;

Solution 2

If you are able to upgrade to SQL*Plus 12.2, you can use set markup csv on:

SQL> set markup csv on
SQL> select * from departments;

"DEPARTMENT_ID","DEPARTMENT_NAME","MANAGER_ID","LOCATION_ID"
10,"Administration",200,1700
20,"Marketing",201,1800
30,"Purchasing",114,1700
40,"Human Resources",203,2400
50,"Shipping",121,1500
60,"IT",103,1400
70,"Public Relations",204,2700
80,"Sales",145,2500
90,"Executive",100,1700
100,"Finance",108,1700

Comments

[user4758229]

Is there anyway to enclose text with double quotes???

[user4758229]

if the text has double quote in middle ex: DARIUSZ "DAREK" VALSTED –

[user4758229]

if the text has double quote in middle ex: DARIUSZ "DAREK" VALSTED

[bob dylan]

Yes - @user4758229 SQL> select * from t1; DARIUSZ "DAREK" VALSTED SQL> select '"'||col1||'"' from t1; "DARIUSZ "DAREK" VALSTED"

[MT0]

@user4758229 Just escape them by doubling up the double-quote characters using REPLACE.

[William Robertson]

You only need SQL*Plus 12.2 for this, it doesn't need a database upgrade.