COALESCE Fallbacks: Empty Text Is Still a Supplied Value

COALESCE fallbacks choose the first non-NULL value, not the first value that looks useful on screen. I decide whether empty text counts as supplied before building a customer display name.

Give the three sources a priority

Suppose a contact extract offers a preferred name, a legal name and a contact address. The preferred name should win when available. Otherwise the legal name comes next, followed by the contact address.

The eight rows include an ordinary preferred name, a missing preferred name, empty text, two spaces and entirely missing fields. Every source has the same explicit nvarchar(30) type. That keeps availability separate from differing expression widths.

WITH Names AS
(
    SELECT CaseId,CAST(Preferred AS nvarchar(30)) AS Preferred,
           CAST(Legal AS nvarchar(30)) AS Legal,
           CAST(Contact AS nvarchar(30)) AS Contact
    FROM (VALUES
        (1,N'Mira',N'Mira Patel',N'mira@example.test'),
        (2,NULL,N'Ravi Shah',N'ravi@example.test'),
        (3,N'',N'Legal Name',N'empty@example.test'),
        (4,N'  ',N'Space Name',N'spaces@example.test'),
        (5,NULL,N'',N'contact@example.test'),
        (6,NULL,NULL,NULL),
        (7,N'',N'',N''),
        (8,NULL,NULL,N'last@example.test')
    ) AS v(CaseId,Preferred,Legal,Contact)
), Resolved AS
(
    SELECT *,COALESCE(Preferred,Legal,Contact) AS DisplayName FROM Names
)
SELECT CaseId,Preferred,Legal,Contact,DisplayName,
       DATALENGTH(DisplayName) AS DisplayBytes
FROM Resolved ORDER BY CaseId;

The first row returns Mira. The second returns Ravi Shah because Preferred is NULL. The eighth reaches last@example.test because both earlier fields are missing. Changing the priority would change these decisions even if the source values stayed the same.

Empty text stops the fallback

Case three contains an empty preferred name. COALESCE returns that empty value, even though Legal Name is available. Empty text is a supplied string, so the function does not continue to the next argument.

Case five behaves similarly at the second position. The preferred name is missing, but the legal name is empty. The contact address is not selected. A blank display cell can hide which input ended the search.

DATALENGTH makes the distinction visible. Empty nvarchar text has zero bytes. The fourth row contains two ordinary spaces and has four bytes. A client may show both outputs as blank, although they are different supplied values.

The entirely missing row returns NULL, with a NULL byte count. The row containing three empty strings returns empty text and zero bytes. I retain both cases because an application can assign different meanings to an unanswered field and a deliberately supplied empty field.

Normalize only the values the contract calls missing

If the interface defines an empty string as unavailable, apply that rule before choosing the first available value. The next query converts exactly zero-byte strings to typed NULL. It leaves populated text and whitespace untouched.

WITH Names AS
(
    SELECT CaseId,CAST(Preferred AS nvarchar(30)) AS Preferred,
           CAST(Legal AS nvarchar(30)) AS Legal,
           CAST(Contact AS nvarchar(30)) AS Contact
    FROM (VALUES
        (1,N'Mira',N'Mira Patel',N'mira@example.test'),
        (2,NULL,N'Ravi Shah',N'ravi@example.test'),
        (3,N'',N'Legal Name',N'empty@example.test'),
        (4,N'  ',N'Space Name',N'spaces@example.test'),
        (5,NULL,N'',N'contact@example.test'),
        (6,NULL,NULL,NULL),
        (7,N'',N'',N''),
        (8,NULL,NULL,N'last@example.test')
    ) AS v(CaseId,Preferred,Legal,Contact)
), Normalized AS
(
    SELECT CaseId,
           CASE WHEN DATALENGTH(Preferred)=0 THEN CAST(NULL AS nvarchar(30)) ELSE Preferred END AS Preferred,
           CASE WHEN DATALENGTH(Legal)=0 THEN CAST(NULL AS nvarchar(30)) ELSE Legal END AS Legal,
           CASE WHEN DATALENGTH(Contact)=0 THEN CAST(NULL AS nvarchar(30)) ELSE Contact END AS Contact
    FROM Names
), Resolved AS
(
    SELECT CaseId,COALESCE(Preferred,Legal,Contact) AS DisplayName FROM Normalized
)
SELECT CaseId,DisplayName,DATALENGTH(DisplayName) AS DisplayBytes
FROM Resolved ORDER BY CaseId;

The formerly empty preferred name falls through to Legal Name. The empty legal name in case five falls through to contact@example.test. Case seven becomes NULL because every supplied string was empty under the selected normalization rule.

The two-space preferred name still wins. Its byte length is positive, so this policy does not discard it. If whitespace should also count as missing, define and test that broader rule separately instead of quietly adding it here.

I use DATALENGTH to test exact emptiness, rather than a displayed character count. LEN excludes trailing spaces, so it can classify a spaces-only value differently. Text equality also follows its comparison rules and should not replace this byte-length condition without checking the intended contract.

Keep fallback selection within its scope

A fallback does not verify that a name is genuine or an address can receive messages. It selects among supplied values under a stated priority. Source validation still needs requirements for accepted characters, length and content.

The examples use simple column expressions from inline rows. They do not establish evaluation behavior for volatile subqueries or concurrent changes. COALESCE has type-selection and evaluation rules beyond this availability lesson, so a more complex expression needs separate review.

Both queries read values without creating objects or changing connection options. CaseId fixes the sequence, and every input stays available in the first result. Compare the empty, spaces-only and all-missing cases before adapting the second query to an interface.

Choose the missing-value policy first, then let the fallback priority express it consistently.

A fallback is not a cleanup rule, it is a priority applied to supplied values.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.