newleaf newleaf - 1 month ago 11
SQL Question

SQL coalesce with empty string ,NULLIF is not usable in my system

Try to use coalesce in my query, for example I want the following query

COALESCE('', '','THIS')


give
'THIS'
, but it turns out to be
''


Found one solution SQL Coalesce with empty string, but my system could not accept NULLIF.

Answer

NULLIF() is not a standard SQL function, so it won't work depending on the brand of RDBMS you use.

The equivalent in standard SQL would be:

COALESCE(CASE columnname WHEN '' THEN NULL ELSE columnname END, 'THIS')

Or you might as well do this:

CASE columnname WHEN '' THEN 'THIS' ELSE columnname END
Comments