Replacing "(null)" with an empty string in the result of an SQL query
I believe what I'm asking here, but I haven't found a way yet:
Some cells as a result of my SQL SELECT-FROM-WHERE query are empty, while the DbVisualizer is (null)
written internally. Instead, I want to display a blank cell.
I already tried using CASE-WHEN and NVL operator, but it won't let me replace it with empty one ''
, I am forced to use some ' '
or 'message'
.
I know I can just remove those spaces or messages using Excel later, but I would like to know if there is a way to do it directly from DbVisualizer instead of this workaround.
EDIT: This 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*
Thank you so much!
source to share
Your query will have the following ELSE part of the CASE expression :
ELSE
''
In Oracle, an empty string is considered NULL . So, all you have to do is use something else instead ''
.
For example, to use space instead of NULL :
ELSE
' '
Update The problem is the DbVisualizer tool . OP is on version 8.0.12
. Prior to version, 9.2.8
it cannot display NULL as an empty string. However, as discussed in this forum , it was fixed in DbVisualizer 9.2.8 .
source to share
Have you tried the standard SQL coalesce () function like below?
SELECT COALESCE(columnName, '') AS ColumnName FROM tableName;
Syntax:
COALESCE (expr1, expr2)
equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
Similarly,
COALESCE (expr1, expr2, ..., exprn), for n>=3
equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn) END
Above are examples from SQL Language Reference for Databases
source to share
Standard SQL provides COALESCE (expr1, expr2, ...) as suggested by @Shishir.
COALESCE()
takes a variable number of arguments and returns the first expression that NOT NULL
MySQL also provides IFNULL (expr1, expr2) which returns expr2
whenexpr1
IS NULL
<strong> Examples
SELECT
COALESCE(field1, ''),
COALESCE(field1, field2, field3)
IFNULL(field1, ''),
IFNULL(field1, field2)
FROM table
source to share