CODEWITHSUNDEEP
sql-serverisnull
Akshay
Akshay

Reputation: 50

SQL Server - Unexpected behavior with ISNULL/NULLIF

Trying to understand the reason for the below behavior in SQL Server.

The below expression returns '*' on my server: Select IsNull(NullIF(RTrim(NULL), ' '), '**')

Shouldn't it be returning '**'?

If I use any of the below statements instead, it correctly returns '**'.

  1. Using sql_variant variable for RTrim(NULL)

declare @a sql_variant = RTRIM(NULL) Select IsNull(NullIF(@a, ' '), '**')

  1. Using a char(2) variable

declare @a char(2) Set @a = NULL Select IsNull(NullIF(RTrim(@a), ' '), '**')

SQL Version: SQL Server 2008 SP3 ANSI Settings: Default SSMS settings.

Upvotes: 0

Views: 499

Answers (2)

Eray Balkanli
Eray Balkanli

Reputation: 7960

Because only the first character is returned at your query since there is no initial data type for the parameter null. Try:

Select IsNull(NullIF(RTrim(cast(NULL as varchar(max))), '  '), '**')
Select IsNull(NullIF(RTrim(cast(NULL as varchar(max))), '  '), '*****')

Upvotes: 1

Thom A
Thom A

Reputation: 95564

The reason is because your NULL has no initial data type, thus, when SQL Server converts it to a varchar, it effectively converts it into varchar(1). Hence the value returned is only '*'.

To get the expected behaviour you need to let SQL Server know what the data type of the NULL is. For example, by declaring a variable (as you did) or use CAST or CONVERT:

DECLARE @String varchar(2) = NULL;

SELECT ISNULL(NULLIF(RTRIM(@String), '  '), '**');
SELECT ISNULL(NULLIF(RTRIM(CONVERT(varchar(2),NULL)), '  '), '**');

Upvotes: 2

Related Questions