Question: I am running a SQL query and I
get improperly formatted NULL output, displayed as back to back
1 51 10 ""
51 10 "JACK"
52 10 "BOB"
52 10 ""
51 11 ""
How can I remove these double quotes from my SQL output?
There are many ways to remove unwanted NULL values in SQL output:
- SQL*Plus column format command
- decode or case statements
- where clause filtering
I would start by experimenting with the
NVL operator to
suppress a NULL value. The Oracle NULL Value (NVL) SQL operator is
a great way to substitute NULL values with numeric values or a blank
string in Oracle SQL statements.
The most common use for the NULL value (NVL) clause is to
prevent mathematical operations from aborting with a zero divisor.
Here are examples of the NVL SQL operator:
nvl(b.buffer_gets,0) – NVL replaces a NULL value with a zero
select nvl(current_status, “Not disclosed”) –
NVL replaces a NULL value with a string
As you can see, the NVL function replaces a NULL with
whatever value you desire.
If your output is in SQL*Plus, you can use the
and format command to suppress the display of any column.
I'm not positive, but you may be able to use
change the NULL's to a blank.
Lastly, you can suppress a NULL value by using a where
clause if doing an aggregate:
where mycol is not null or
length(trim(mycol)) > 0