Help setting null character, tab delimiter, removing space padding

[jeffshih]

Hello:

I'm using a script in SQL*Plus 8.1.6.0.0 to spool results in a very specific format. The formatting problem I'm having is getting all 3 of the following requirements met simultaneously:
1. trimming leading and trailing space padding for all columns, and
2. having a tab as my column delimiter, and
3. having a dash "-" to represent my nulls (rather than a blank)
I know how to achieve these formats independently, but can't seem to get them to work together. At the moment, I can only get 2 out of 3 working together.

For example, this will not trim space padding from each column, but will have dashed nulls and tab delimiters...

set null "-";
set trimout on;
set colsep "	";
set lines 1000 trimspool on heading off termout off feedback off sqlprompt '' wrap off pagesize 0;
spool d:\regi.out;
select signuptime,lower(state),lower(sex)
from foobar;
spool off;
exit;

...and this will not have dashed nulls even though I'm explicitly setting it, but will have space padding removed and tab column delimiters.

set null "-";
set lines 1000 trimspool on heading off termout off feedback off sqlprompt '' wrap off pagesize 0;
spool d:\regi.out;
select signuptime||chr(9)||lower(state)||chr(9)||lower(sex)
from foobar;
spool off;
exit;

I'm probably missing something ridiculously minor that's holding me back, but can't figure it out. Thanks in advance for any insight and assistance!

 

[SantaMufasa]

Jeff,

If your second option is doing everything you want except for the NULL values, then you can use the following "Null-value" function:

select nvl(signuptime,'-')||chr(9)||nvl(lower(state),'-')||chr(9)||nvl(lower(sex),'-')
from foobar;

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:23 (13Mar04) UTC (aka "GMT" and "Zulu"), 17:23 (12Mar04) Mountain Time)

 

[jeffshih]

Thank you, Mufasa -- the nvl function did it!

I had to apply the to_char function to the non-char column (see below), but I was able to get the desired result.

nvl(to_char(signuptime,'DD-Mon-YYYY HH24:MI:SS'),'-')