Why IsNull(LTrim(RTrim(Lower(null))), -1) is *?

Question

Today I was testing something at work place and came across this one

Case 1:

Declare @a nvarchar(20)
Set @a = null
Select IsNull(LTrim(RTrim(Lower(@a))), -1)

Case 2:

Select IsNull(LTrim(RTrim(Lower(null))), -1)

The result in case 1 is -1 but * in case 2 I was expecting same results in both cases. Any reason?

Answer 1

Without the declaration of data type, null in this case is declared as varchar(1). You can observe this by selecting the results into a #temp table:

Select IsNull(LTrim(RTrim(Lower(null))), -1) as x INTO #x;
EXEC tempdb..sp_help '#x';

Among the results you'll see:

Column_name   Type      Length
-----------   -------   ------
x             varchar   1

Since -1 can't fit in a varchar(1), you are getting * as output. This is similar to:

SELECT CONVERT(VARCHAR(1), -1);

If you want to collapse to a string, then I suggest enclosing the integer in single quotes so there is no confusion caused by integer <-> string conversions that aren't intended:

SELECT CONVERT(VARCHAR(1), '-1'); -- yields "-"
SELECT CONVERT(VARCHAR(30), '-1'); -- yields "-1"

I would not make any assumptions about how SQL Server will handle a "value" explicitly provided as null, especially when complex expressions make it difficult to predict which evaluation rules might trump data type precedence.