Today’s blog post came to my mind, as I am re-writing some scripts, written by others, with tones of concatenations of commas, to obtain a csv file. I am improving the scripts, by replacing tens of lines of code with just one line.

How many times have you started writing SQL statements, and concatenated columns with the comma character, to obtain a comma delimited file? I used to do that too! Until I knew better, and remembered that SQLPLUS has an option, to set a separator for the columns, a text to print between columns.

By default the text that is printed between columns is a single space. You can change this to anything you want. One of the most used separators would be the comma, but really this could be anything, a semicolon, a colon, two spaces, the letter A, anything you want.

Instead of this:

select USER_ID  || ',' ||  
       USERNAME || ',' || 
       ACCOUNT_STATUS || ',' ||  
       PROFILE 
from dba_users 
order by username;

USER_ID||','||USERNAME||','||ACCOUNT_STATUS||','||PROFILE
----------------------------------------------------------
46,ANONYMOUS,EXPIRED & LOCKED,DEFAULT
31,APPQOSSYS,EXPIRED & LOCKED,DEFAULT
43,CTXSYS,EXPIRED & LOCKED,DEFAULT
30,DBSNMP,OPEN,DEFAULT

Use this:

set colsep ","

select USER_ID, 
      USERNAME, 
      ACCOUNT_STATUS, 
      PROFILE 
from dba_users 
order by username;

  USER_ID,USERNAME  ,ACCOUNT_STATUS                  ,PROFILE
----------,----------,--------------------------------,-------------
       46,ANONYMOUS ,EXPIRED & LOCKED                ,DEFAULT
       31,APPQOSSYS ,EXPIRED & LOCKED                ,DEFAULT
       43,CTXSYS    ,EXPIRED & LOCKED                ,DEFAULT
       30,DBSNMP    ,OPEN                            ,DEFAULT

You can also use other separators:

set colsep ";"

select USER_ID, 
      USERNAME, 
      ACCOUNT_STATUS, 
      PROFILE 
from dba_users 
order by username;

  USER_ID;USERNAME  ;ACCOUNT_STATUS                  ;PROFILE
----------;----------;--------------------------------;-------------
       46;ANONYMOUS ;EXPIRED & LOCKED                ;DEFAULT
       31;APPQOSSYS ;EXPIRED & LOCKED                ;DEFAULT
       43;CTXSYS    ;EXPIRED & LOCKED                ;DEFAULT
       30;DBSNMP    ;OPEN                            ;DEFAULT

The benefit of using colsep is that your code is much easier to read, the columns are better aligned and even the heading of the columns will be comma (or other character) separated! In one quick sentence REPLACE || ‘,’ || WITH set colsep ‘,’

Have you used colsep before?