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)