SQL Coalesce with empty string

SqlSql Server

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

Use a CASE expression or NULLIF:

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

Solution 2 - Sql

try this

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

Solution 3 - Sql

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

Categories
Recommended Sql Solutions
Recommended Sql Server Solutions

Previous Solution:

Mongoose: CastError: Cast to ObjectId failed for value "[object Object]" at path "_id"

node.jsMongodbMongoose

Next Solution:

mobile chrome fires resize event on scroll

AndroidJqueryGoogle ChromeScroll

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionNate PetView Question on Stackoverflow
Solution 1 - SqlLamakView Answer on Stackoverflow
Solution 2 - Sqlrs.View Answer on Stackoverflow
Solution 3 - SqlMatBailieView Answer on Stackoverflow