isnull function in WHERE clause

Question

I am attempting to fix an issue in a stored procedure and have come across an issue that is vexing me.

Basically, isnull works as expected for one record in T0 but not in another, both where T0.FatherCard are NULL. I cannot see why.

SELECT *
FROM OINV T0
WHERE ISNULL(T0.FatherCard, T0.CardCode) = 'C0189'

Returns a full row of data as expected.

SELECT *
FROM OINV T0
WHERE ISNULL(T0.FatherCard, T0.CardCode) = 'C0817'

Returns nothing. I am expecting a full row of data.

In both cases, FatherCard = NULL

CardCode    FatherCard  Table
------------------------------
C0189       NULL        OINV
C0817       NULL        OINV

FatherCard and CardCode are both of the same type (nvarchar) and length (50).

If I remove the ISNULL function and simply select WHERE T0.CardCode = C0817 then it works as expected.

Is it possible T0.FatherCard is actually not NULL for the purposes of the ISNULL evaluation, and is returning some other value?

Answer 1

There are 2 possibilities.

1. FatherCard may have the string value "NULL" and not actually be NULL.
2. You could have extraneous spaces at the end of C0817 I.e. 'C0817 '

To check use:

SELECT  '[' + CardCode + ']', ISNULL(FatherCard, 'Yes is NULL')
FROM    OINV
WHERE   RTRIM(CardCode) = 'C0817'