Replacing "(null)" with blank cell in SQL query result

Question

I believe what I'm asking here is possible, but I haven't find a way yet :

Some cells in the result of my SQL SELECT-FROM-WHERE query are blank, and in DbVisualizer, (null) is written inside. I'd like to display a blank cell instead.
I've already tried with CASE-WHEN and the NVL operator, but it won't let me replace it by a blank '', I'm forced to use some ' ' or 'message'.

I know I could just delete these spaces or messages with Excel later, but I'd like to know if there is a way to do it directly with DbVisualizer, instead of this workaround.

EDIT: Here is what my request looks like :

SELECT *things*,
  CASE WHEN
     (SELECT COUNT(*) FROM table d2 WHERE *join-condition* AND *other condition*) = 1
  THEN
     (*sub-select query*)
  ELSE
     ''
  END
  AS NAME,
  *other things*
  FROM table d1
  WHERE *something*

Thanks a lot !

In tsql `null` and `''` are not same...... in Oracle I think `''` and `null` are same. [link](http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null) — Praveen, Jul 14 '15 at 10:28
Thats correct in Oracle, ''=null. @BusyAnt please specify the database - is it Oracle? — Nick.Mc, Jul 14 '15 at 10:37

Shishir Kumar · Answer 1 · 2015-07-14T10:34:44.670

4

Did you try standard SQL function coalesce(), as below ?

SELECT COALESCE(columnName, '') AS ColumnName FROM tableName;

Syntax:

COALESCE Syntax

COALESCE (expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Similarly,

COALESCE (expr1, expr2, ..., exprn), for n>=3

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END

Above examples are from Database SQL Language Reference

edited Jul 14 '15 at 10:34

answered Jul 14 '15 at 10:28

Shishir Kumar

7,981
3
29
45

*coalesce* is like a big *CASE-WHEN*, isn't it? what I see I could do with it is `coalesce(NULL,'')` so that all the cells with `(null)` will become empty. Is that what you thought ? My query is a bit Special, I will edit my question to make it more clear – AdrienW Jul 14 '15 at 10:40
1

coalesce() is the not-null for standard SQL. If you want to make all cells with `null` as empty, then in your select query you will write `SELECT COALESCE(columnName, '') AS ColumnName FROM tableName;` where columnName is the cell with null values. – Shishir Kumar Jul 14 '15 at 10:49
Thanks, that may be useful,but do you think I can directly apply it to my query ? (see edit) – AdrienW Jul 14 '15 at 10:56
1

@ShishirKumar Actually, the issue has nothing to do with the database, but with the DbVisualizer client tool. – Lalit Kumar B Jul 14 '15 at 11:25

Lalit Kumar B · Accepted Answer · 2015-07-14T11:24:15.607

2

The problem in your query is the following ELSE part of the CASE expression:

 ELSE
     ''

In Oracle, an empty string is considered as NULL value. So, all you need to do is use something else instead of ''.

For example, to use a space instead of NULL:

ELSE 
   ' '

Update The issue is the DbVisualizer tool. OP is on version 8.0.12. Prior to version 9.2.8 it cannot show NULL as an empty string. However, as discussed in this forum, it has been fixed in DbVisualizer 9.2.8.

edited Jul 14 '15 at 11:24

answered Jul 14 '15 at 11:07

Lalit Kumar B

47,486
13
97
124

I've thought about that, but the thing is : I want to avoid using **space**, and really display a cell that contains nothing, for *.csv* exportation purposes – AdrienW Jul 14 '15 at 11:16
@BusyAnt Explain *nothing*. – Lalit Kumar B Jul 14 '15 at 11:17
Well, *blank*, *empty*, so that when I export it into Excel the cells are empty – AdrienW Jul 14 '15 at 11:19
1

Then you need to upgrade your client to `9.2.8` as you can modify the settings to show `NULL` as empty string. – Lalit Kumar B Jul 14 '15 at 11:20
I've got only `8.0.12 Free`, you're right, thanks a lot ! – AdrienW Jul 14 '15 at 11:22

Michel Feldheim · Answer 3 · 2015-07-14T10:43:44.097

1

Standard SQL provides COALESCE(expr1, expr2, ...) as suggested by @Shishir.

COALESCE() takes a variable amount of arguments and returns the first expression that is NOT NULL

MySQL also provides IFNULL(expr1,expr2), which returns expr2 when expr1 IS NULL

Examples

SELECT
    COALESCE(field1, ''),
    COALESCE(field1, field2, field3)
    IFNULL(field1, ''),
    IFNULL(field1, field2)
FROM table

edited Jul 14 '15 at 10:43

answered Jul 14 '15 at 10:37

Michel Feldheim

17,625
5
60
77

1

Actually, the issue has nothing to do with the database, but with the DbVisualizer client tool. – Lalit Kumar B Jul 14 '15 at 11:26

Replacing "(null)" with blank cell in SQL query result

3 Answers3