CONCAT: Missing Inputs Produce Present Text

CONCAT treats missing inputs as empty strings, so a present output does not prove every source value was present. I retain source availability when it matters. Joining text and validating completeness answer different questions.

Compare complete and incomplete source pairs

The first row joins AB with CD. Its expected output is ABCD with eight bytes of Unicode text. Neither missing-input flag is set.

The second row joins AB with a missing right input. Its expected output is AB rather than SQL NULL. The right-side missing flag remains one.

The third row reverses that situation, joining a missing left input with CD. Its expected output is CD. The available text survives even though the pair is incomplete.

I return both source values and separate missing flags. That prevents the combined display from becoming the only record of input availability. The output string cannot reliably reconstruct which source was absent.

WITH Inputs AS
(
    SELECT CaseId,LeftText,RightText FROM (VALUES
        (1,CAST(N'AB' AS nvarchar(8)),CAST(N'CD' AS nvarchar(8))),
        (2,CAST(N'AB' AS nvarchar(8)),CAST(NULL AS nvarchar(8))),
        (3,CAST(NULL AS nvarchar(8)),CAST(N'CD' AS nvarchar(8))),
        (4,CAST(NULL AS nvarchar(8)),CAST(NULL AS nvarchar(8))),
        (5,CAST(N'' AS nvarchar(8)),CAST(N'CD' AS nvarchar(8)))
    ) AS v(CaseId,LeftText,RightText)
), Combined AS
(
    SELECT *,CONCAT(LeftText,RightText) AS CombinedText FROM Inputs
)
SELECT CaseId,LeftText,RightText,CombinedText,DATALENGTH(CombinedText) AS OutputBytes,
    CASE WHEN LeftText IS NULL THEN 1 ELSE 0 END AS LeftMissing,
    CASE WHEN RightText IS NULL THEN 1 ELSE 0 END AS RightMissing
FROM Combined ORDER BY CaseId;

All missing inputs still produce empty text

The fourth row supplies two missing Unicode inputs. Its expected combined text is empty and its expected byte count is zero. Both missing flags remain one.

The additional scalar query supplies two missing varchar inputs. It also expects an empty string and zero bytes. With all NULL inputs, CONCAT returns an empty varchar(1) value.

An application that treats non-NULL output as proof of a completed record would therefore misclassify this result. The function has performed concatenation successfully. It has not validated required fields.

I use byte count rather than a grid’s blank appearance to expose the empty result. A missing output and an empty output can look similar. Their source meanings are still different.

SELECT CONCAT(CAST(NULL AS varchar(8)),CAST(NULL AS varchar(8))) AS AllMissingText,
    DATALENGTH(CONCAT(CAST(NULL AS varchar(8)),CAST(NULL AS varchar(8)))) AS OutputBytes;

Empty source text can hide a different condition

The fifth row supplies an empty left value and CD on the right. Its expected combined text matches the third row’s CD. Both missing flags are zero.

Those two identical strings arise from different input states. One left value is missing and the other is present but empty. A concatenated display is therefore not a unique encoding of the source pair.

If that distinction belongs to an interface contract, preserve it separately. Explicit field objects or source-status columns can carry more information. Plain concatenation alone cannot preserve every boundary and absence state.

This example intentionally supplies no separator. A separator would change the displayed string but would not automatically validate the source fields. Formatting remains separate from completeness.

Use concatenation for a deliberate display policy

CONCAT converts supplied values into strings before joining them. The return type and capacity depend on the input types. These examples use short explicit text types rather than relying on numeric display conversions.

I wouldn’t infer a general serialization format from this sample. Different source pairs can produce the same combined text. A reversible encoding needs an additional contract for field boundaries and missing values.

For a human-readable label, keeping available parts can be exactly the required behavior. Validate mandatory parts before declaring the label complete. That preserves the convenience without hiding an incomplete source.

Keep partially missing, entirely missing and empty-source pairs separate. Their combined labels can conceal different source states. Retain the availability flags when completeness belongs to the receiving contract.

Keep a flag for what went missing, and the label stays honest.

A present combined label is not proof of complete inputs, it is only the available parts.

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