NULLIF Text: Collation Decides Whether Values Match

NULLIF text comparisons use a matching rule, and a matching pair produces a NULL of the first expression’s type. I make the comparison collation explicit. Returning the first value is a different contract from returning the wider second value.

Choose whether letter case establishes equality

The first pair contains uppercase AB and lowercase ab. Both values are Unicode strings with explicit lengths. The first expression is nvarchar(4), while the second is nvarchar(12).

Under the case-insensitive collation, that pair compares as equal. Its expected IgnoreCase result is NULL. Under the case-sensitive collation, the expected RespectCase result is the original AB.

The second pair contains two identical uppercase AB strings. Both comparison rules expect NULL. The third pair compares AB with XYZ and expects AB under both rules.

I display the original inputs beside the results. That exposes which value is retained when the comparison does not match. The function does not return the second argument as an alternative value.

WITH Inputs AS
(
    SELECT CaseId,FirstText,SecondText FROM (VALUES
        (1,CAST(N'AB' AS nvarchar(4)),CAST(N'ab' AS nvarchar(12))),
        (2,CAST(N'AB' AS nvarchar(4)),CAST(N'AB' AS nvarchar(12))),
        (3,CAST(N'AB' AS nvarchar(4)),CAST(N'XYZ' AS nvarchar(12))),
        (4,CAST(NULL AS nvarchar(4)),CAST(N'AB' AS nvarchar(12))),
        (5,CAST(N'AB' AS nvarchar(4)),CAST(NULL AS nvarchar(12)))
    ) AS v(CaseId,FirstText,SecondText)
), Compared AS
(
    SELECT *,NULLIF(FirstText COLLATE Latin1_General_100_CI_AS,SecondText COLLATE Latin1_General_100_CI_AS) AS IgnoreCase,
        NULLIF(FirstText COLLATE Latin1_General_100_CS_AS,SecondText COLLATE Latin1_General_100_CS_AS) AS RespectCase
    FROM Inputs
)
SELECT CaseId,FirstText,SecondText,IgnoreCase,RespectCase,
    CAST(SQL_VARIANT_PROPERTY(CAST(RespectCase AS sql_variant),'MaxLength') AS int) AS ResultMaximumBytes
FROM Compared ORDER BY CaseId;

Keep the first expression’s type contract

The wider second string participates in the comparison. It does not determine NULLIF’s return type. The result has the type of the first expression.

ResultMaximumBytes inspects the case-sensitive result when it is present. Its expected capacity is eight bytes for nvarchar(4). The second argument’s twenty-four-byte capacity is not the output contract.

The property call returns NULL when RespectCase itself is NULL. That value-based inspection does not mean the compile-time return type vanished. It simply has no present result value to inspect.

I keep this distinction visible because a displayed AB cannot reveal the expression’s full width. Two equal-looking outputs can carry different capacities. The source type belongs in a review of later assignments.

Missing arguments do not mean the pair matched

The fourth pair has a missing first value and a present second value. Its expected results remain NULL. That outcome alone cannot prove that the two expressions compared as equal.

The fifth pair has a present first value and a missing second value. The ordinary equality comparison is Unknown. Both expected results retain the first value AB.

This query therefore distinguishes a match-generated NULL from a missing original first value. An application that needs the cause should preserve source status separately. A single output value cannot reconstruct every comparison outcome.

I do not replace missing arguments with empty text before comparing. That would introduce another normalization policy. The example keeps the original missing states visible.

Use NULLIF for a defined equivalence rule

A placeholder-normalization rule can use NULLIF when the placeholder and matching policy are explicit. Case-insensitive removal may suit one contract. A case-sensitive token contract can require a different result.

The collation choices in this query apply to expressions only. They change no database or server configuration. They let the expected case behavior remain independent of the default collation.

NULLIF behaves like a searched CASE for this comparison. This example uses safe deterministic text expressions. It does not rely on repeated evaluation of a volatile input.

Select the matching policy before using NULLIF to remove a placeholder. Keep differing text and missing arguments separate from a successful match. The first expression’s type remains part of the resulting contract.

When adapting a normalization rule, test a case-only difference and a completely different value. Keep a missing first argument and a missing second argument separate. Confirm the first expression’s type before assigning the result downstream.

Pick the matching rule on purpose, and NULLIF stays predictable.

A NULLIF result is not a choice between two types, it is the first expression’s type.

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