How to COALESCE for empty strings and NULL values?

sql

sql-functions

performance-optimization

best-practices

byAnton Shumikhin·Dec 19, 2024

To make NULL or empty strings great again (by converting them to something meaningful), use NULLIF to convert empty strings into NULL and apply COALESCE with your fallback value:

-- Handles both "" and NULL, replacing them with 'fallback'
SELECT COALESCE(NULLIF(column, ''), 'fallback') FROM table;

If you're wrestling with multiple columns, use NULLIF within COALESCE for each of them:

-- Returns the name of the one that didn't chicken out (first valid value)
SELECT COALESCE(NULLIF(col1, ''), NULLIF(col2, ''), 'fallback') FROM table;

Swift tweaks for swift performance

Now, you may have heard the rumor: that user functions are like molasses in January, while native SQL functions are as fast as roadrunners. It's true—almost. Optimized user functions can give native SQL functions a run for their money.

Let's dive deeper

Deploy COALESCE and NULLIF side by side

Keep NULLIF(column_name, '') close to your COALESCE. The duo champions against both trifling NULLs and empty strings:

-- First non-empty and non-null guard wins! If all chicken out, 'none' wins.
SELECT COALESCE(NULLIF(col1, ''), NULLIF(col2, ''), 'none') FROM table;

Make functions fast, not furious

Need to juggle multiple columns and still beat the clock? Custom scalar functions at your rescue:

-- The Flash of functions: As quick as native SQL, with some user flair
CREATE FUNCTION dbo.OptimizedCoalesce(@col1 VARCHAR(MAX), @col2 VARCHAR(MAX), ...)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN COALESCE(NULLIF(@col1, ''), NULLIF(@col2, ''), ...,'fallback')
END;

Be sure to test this function against a Timex (native SQL) to ensure speed.

Don't forget scalability

If you're dealing with variable length lists, opt for a table-valued function or dynamic SQL.

Mind the tricky corner cases

There might be occasions when even empty strings have significance. Thinking about whether you want to treat them differently from NULL ahead of time can save headaches later.

explain-codes / Sql / How to COALESCE for empty strings and NULL values?

Linked

Mysql: Typecasting NULL to 0



Coalesce Function in TSQL



If-then-else statements in postgresql



Replacing NULL with 0 in a SQL server query



T-sql Substring - Last 3 Characters

