SQL Coalesce with empty string

I have the following:

Select Coalesce(Other,Industry) Ind from registration

The thing is that Other can be an empty string or NULL. How do I get coalesce to work such that if Other is an empty string, Coalesce still returns Industry?

Solution 1:

Use a CASE expression or NULLIF:

SELECT COALESCE(NULLIF(Other,''),Industry) Ind FROM registration

Solution 2:

try this

Select Coalesce(nullif(Other,''),Industry) Ind from registration

Solution 3:

You can also use a short-cut knowing that NULL <> '' doesn't evaluate to TRUE...

CASE WHEN other <> '' THEN other ELSE industry END

The logic then works out as follows...

• CASE WHEN 'fubar' <> '' THEN other ELSE industry END
  => CASE WHEN true THEN other ELSE industry END
  => other

• CASE WHEN '' <> '' THEN other ELSE industry END
  => CASE WHEN false THEN other ELSE industry END
  => industry

• CASE WHEN NULL <> '' THEN other ELSE industry END
  => CASE WHEN NULL THEN other ELSE industry END
  => industry