Question: I am running a SQL query and I
get improperly formatted NULL output, displayed as back to back
double quotes:
1 51 10 ""
1
51 10 "JACK"
2
52 10 "BOB"
2
52 10 ""
3 51
11 "BILL"
3
51 11 ""
4 52
999 ""
How can I remove these double quotes from my SQL output?
Answer:
There are many ways to remove unwanted NULL values in SQL output:
- NVL
- 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:
select
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
column
and format command to suppress the display of any column.
I'm not positive, but you may be able to use
decode to
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
