Search code examples
sqlsql-serversql-server-2014coalesce

Is there a succinct way of performing an identical coalesce on all columns of an SQL query?

Say I have a query:

SELECT FirstName, LastName, Sex, Age FROM Human

If for any reason there's a null in the table for a value we can:

coalesce(FirstName, 'Unknown')

For larger queries with far more columns, instead of coalescing each column individually is there a way to coalesce each individual column, but without duplicating the amount of coalesce operations?

Something like this:

SELECT coalesce(FirstName, LastName, Sex, Age, 'Unknown') FROM Human

(I know that's wrong and wont work, it's part of my example trying to illustrate my intentions).

Ideally the above would check each column in-turn and replace any nulls with 'Unknown'. Is there an easy way of doing that rather than this:

SELECT coalesce(FirstName,'Unknown'), coalesce(LastName,'Unknown'), coalesce(Sex, 'Unspecified'), coalesce(Age,'0') FROM Human

Also, going back to this example:

SELECT coalesce(FirstName, LastName, Sex, Age, 'Unknown') FROM Human

Is there a way of applying an over-arching coalesce like the above, except for n amount of columns? For example only for FirstName,LastName and Sex, not for the rest.

Solution

  • No, you can't do that. COALESCE "is a syntactic shortcut for the CASE expression."

    The questions you should ask yourself are,

    1. Why do I have these NULL values in the first place?
    2. Why am I handling the presentation of my data in my TSQL? TSQL is a great tool for retrieving relational data using set based operations. TSQL is bad tool for making your data look nice.
    3. What would substituting NULL with 'Unknown' give me, apart from a performance deficit and headaches down the road?