CODEWITHSUNDEEP
sqlsql-server
Nate Pet
Nate Pet

Reputation: 46222

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?

Upvotes: 128

Views: 100752

Answers (3)

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 7

rs.
rs.

Reputation: 27427

try this

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

Upvotes: 46

Lamak
Lamak

Reputation: 70638

Use a CASE expression or NULLIF:

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

Upvotes: 264

Related Questions