Why is T-SQL ISNULL() truncating the string and COALESCE is not?

sql-server tsql coalesce isnull truncation
natenho picture natenho · Sep 19, 2013 · Viewed 8.5k times · Source

Given the following:

SELECT ISNULL('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABC (Why?)
SELECT COALESCE('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABCDEFGHIJ

Why are these statements returning different results?

Answer

mvp picture mvp · Sep 19, 2013

According to Microsoft documentation, for function:

ISNULL(check_expression, replacement_value)

replacement_value must be of a type that is implicitly convertible to the type of check_expression. Note that type for 'xy'+NULL is VARCHAR(3). Because of this your string 'ABCDEFGHIJ' is cast to VARCHAR(3) and thus trimmed.

It sounds strange why it is not VARCHAR(2), but this is the way it is - one character longer than 'xy'. You can play with this SQLFiddle and see for yourself that type for 'xy'+NULL is the same as for expression CASE WHEN 1=2 THEN 'XYZ' ELSE NULL END, which is NULL but is implicitly compatible to VARCHAR(3).

It seems that for expression 'xy'+NULL perceived length can be computed as 'xy' string length (2) plus 1 for every NULL added. For example, type of 'xy'+NULL+NULL is VARCHAR(4), type for 'xy'+NULL+NULL+NULL is VARCHAR(5) and so on - check out this SQLFiddle. This is extremely weird, but that is how MS SQL Server 2008 and 2012 work.

Related questions

COALESCE with NULL

I found this snippet of SQL in a view and I am rather puzzled by it's purpose (actual SQL shortened for brevity): SELECT COALESCE(b.Foo, NULL) AS Foo FROM a LEFT JOIN b ON b.aId=a.Id I …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More