Don’t scold ISNULL ( ) function for Truncate a Replacement value in MSSQL

ISNULL ( check_expression , replacement_value ) #

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

replacement_value can be truncated if replacement_value is longer than check_expression.

Example,

CREATE TABLE #TestISNULL
 ( 
 a CHAR(4), 
 b CHAR(10) 
 )
INSERT INTO #TestISNULL 
VALUES (‘1234’,’1234567890'), 
       (NULL,’1234567890')
SELECT a, b, ISNULL(a, b) AS Result
FROM #TestISNULL
DROP table #TestISNULL

Output of above code is,

lets take first row,

ISNULL ( a, b )  
// a is not null so function returned 123, which is value of a  as Result

Now look second row

ISNULL ( a, b )  
// a is null so function want to return value of b

but above function returned 1234 only not full value of b. Why ? Because b (replacement value char 10) is longer than a (check expression char 4).

So how we can achieve our requirement ?

CREATE TABLE #TestISNULL
 ( 
 a CHAR(4), 
 b CHAR(10) 
 )
INSERT INTO #TestISNULL 
VALUES (‘123’,’1234567890'), 
 (NULL,’1234567890')
SELECT a, 
 b, 
 ISNULL( CAST (a AS VARCHAR), b) AS Result // CAST (a AS VARCHAR)
 
FROM #TestISNULL
DROP table #TestISNULL

I have used CAST (a AS VARCHAR) as check expression in ISNULL Function instead of ‘a’ which length is 4

Output of above code is ,

Because of above change in second row we can get full value of ‘b’ without be truncated

or else we can get same output by using ,

COALESCE(a, b)