Find first non-empty, not-null string | NULLIF matrix

If you're MS SQL Server developer, you probably know some of following functions:

• ISNULL(expr1, expr2) - returns expr2 if expr1 IS NULL; returns expr1 if it IS NOT NULL
• COALESCE(expr1, expr2, ...) - returns first NOT NULL expression; can have more then 2 expressions; returns NULL if all expressions IS NULL
• NULLIF(expr1, expr2) - returns NULL if expr1 and expr2 are equal; returns expr1 if expr1 and expr2 are not equal

Now, have some fun with NULLIF..

NULLIF matrix

First not-null and non-empty string in-line function

Now mixing NULLIF with COALESCE you may construct safe in-line function that returns first not-null and non-empty string or empty string of all strings are empty or nulls. You don't have to build CASE statement or use LEN function (to check if string is not emtpy). See this:

NULLIF (with empty string as second param) changes any empty string into NULL so COALESCE won't return empty text. Last param of COALESCE (empty stirings) protects us from returing NULL if all strings are empty or nulls. Of course you may remove last param (empty string) if you prefer NULL if there is no result.