2

I was testing the isNull function, when I found out that if you concatenate NULL plus string, like this for example:

SELECT isNull(NULL+'123456','I am a flag')

The result is neither '1234' or 'I am a flag', it is 'I am a' instead. The length of the result depends on the length of the concatenated string.

I would like to know the cause for this.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
David Roig
  • 23
  • 3
  • I you concatenate an unknown string with a known string, what do you expect to get? a known or an unknown string? – Jesús López Dec 17 '18 at 16:59
  • Possible duplicate of [Truncation issue with ISNULL function in SQL Server](https://stackoverflow.com/q/39544764/1260204) – Igor Dec 17 '18 at 17:05

2 Answers2

4

Use coalesce():

select coalesce(NULL + '123456', 'I am a flag')

ISNULL() strictly uses the type of the first argument. COALESCE() is more lenient, basically looking at the types of all arguments to ascertain the final type. Plus, COALESCE() is standard SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

ISNULL returns the datatype of the first expression.

In your query you have NULL+'123456', which is a varchar(6) and 'I am a flag', which is a varchar(11). NULL + {expression} = NULL so the second parameter is returned and implicitly cast to a varchar(6), resulting in 'I am a' (the first 6 characters of the second expression).

Thom A
  • 88,727
  • 11
  • 45
  • 75