Why is the result of "SELECT ISNULL(LEFT(NULL, 8), '1234567890')" returned as '1' instead of '1234567890'?

user_3226114 0
Viewed 289

I have this query

SELECT ISNULL(LEFT(NULL, 8), '1234567890')

The result I got is '1' instead of '1234567890'. What am I missing?

I'm using SQL Server 2014.

sql-server sql
2 Answers

one difference between ISNULL and COALESCE is that ISNULL limits the length of parameters to that of first whereas COALESCE won't. try the following to know the difference

DECLARE @i AS VARCHAR(4) = NULL, 
        @j AS VARCHAR(6) = 'abcdefg' 

SELECT ISNULL(@i, @j)
SELECT COALESCE (@i, @j)
user_76989090

To limit the length of the result to 8 characters you need 

SELECT ISNULL(cast(NULL as varchar(8)), '1234567890') ;

datatype of ISNULL result is inferred using the first argument.

user_62199790
Related