P5Coder P5Coder - 4 months ago 20
SQL Question

CHARINDEX issue with NULL CHAR variable

DECLARE @MyChar CHAR = NULL

SELECT CHARINDEX(' ', ISNULL(NULL, '')),
CHARINDEX(' ', ISNULL(@MyChar, '')),
CHARINDEX(' ', ISNULL(CONVERT(VARCHAR, @MyChar), ''))


The above query returns the values
0
,
1
and
0
, in that order.

This result should be
0
,
0
and
0
. Is this an issue with MS SQL or there is some functionality here which I haven't understood?

Answer

I belive this will answer the question:

DECLARE @MyChar CHAR = NULL

SELECT  CHARINDEX(' ', ISNULL(NULL, '')) a,
        CHARINDEX(' ', ISNULL(@MyChar, '')) b,
        CHARINDEX(' ', ISNULL(CONVERT(VARCHAR, @MyChar), '')) c

Results:

a           b           c
----------- ----------- -----------
0           1           0

Testing the values:

SELECT  '|' + @MyChar + '|' a,
        '|' + ISNULL(@MyChar, '') + '|' b,
        '|' + ISNULL(CONVERT(VARCHAR, @MyChar), '') + '|' c

Results:

a    b    c
---- ---- --------------------------------
NULL | |  ||

The ISNULL method returns the data type of the first argument it receives. since char has a minimum length of 1, and will pad the value with trailing spaces if needed, the result of ISNULL(@MyChar, '') is a string with a single space, hence the 1 you get in your result.

Comments