Truncation issue with ISNULL function in SQL Serve

问题:

Recently I faced an scenario where ISNULL function is returning me truncated data if the first string is null.

ISNULL(a, b);

I found the a is 5 chars and b is 10 chars but when a is null it will return only 5 chars of b and not full length.

Is this a known issue?

回答1:

It is a known behaviour

From MSDN

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.

Use COALESCE to over come this issue

SELECT COALESCE(a, b) From yourtable

Here is a demo

CREATE TABLE #nulltest 
  ( 
     a CHAR(5), 
     b CHAR(10) 
  ) 

INSERT INTO #nulltest 
VALUES      ('12345','1234567890'), 
            (NULL,'1234567890')   

SELECT a,   
       b, 
       ISNULL(a, b)   AS Isnull_Result, 
       COALESCE(a, b) AS Coalesce_Result 
FROM   #nulltest   

Result :

╔═══════╦════════════╦═══════════════╦═════════════════╗
║   a   ║     b      ║ Isnull_Result ║ Coalesce_Result ║
╠═══════╬════════════╬═══════════════╬═════════════════╣
║ 12345 ║ 1234567890 ║         12345 ║      12345      ║
║ NULL  ║ 1234567890 ║         12345 ║      1234567890 ║
╚═══════╩════════════╩═══════════════╩═════════════════╝

回答2:

Also you can use CASE WHEN with addition to @Prdp answer.

CASE WHEN a is null then b else a end AS caseWhen_Result