Strange behavior with ISNULL () in MSSQL
I have a select statement that concatenates multiple face name segments. This is nothing new.
SELECT FirstName + ' ' + LastName AS FullName FROM MyTable
Then I tried to add an average starting parameter to this and I came up with the following
SELECT FirstName + ' ' + ISNULL(MiddingInitial + ' ', '') + LastName AS FullName FROM MyTable
It works, but ISNULL()
I ran into odd behavior during testing . I know what is NULL + 'any string'
allowed NULL
. However, it was just weird ...
Here is my code and what I get as a result ...
print '''' + isnull(null + 'Any String','Results in null') + ''''
print '''' + isnull(null + 'Any','Results in null') + ''''
print '''' + isnull(null + 'A','Results in null') + ''''
print '''' + isnull(null + '','Results in null') + ''''
/*
'Results in '
'Resu'
'Re'
'Re'
*/
Any idea why this behavior is happening? Does he do the same for you?
source to share
This applies to the data types you are working with and the behavior of the function ISNULL
. Let's look at one example:
null + 'Any String'
The above fits nicely into the data type varchar(11)
. NULL
(this is actually the result char(0)
and has a length of 1) and a regular 10 character string concatenated together is only 11 characters long. The replacement string - the second parameter to your function ISNULL
- will be forced to fit in varchar(11)
, so it is truncated to 11 characters.
The pattern is repeated for the rest of the elements, with a special case for an empty string.
If you don't want this to happen, use COALESCE
which, instead of taking the datatype of the first item in the list, uses datatype precedence. A varchar(15)
takes precedence over a varchar(11)
, so you get the complete replacement string:
print '''' + coalesce(null + 'Any String','Results in null') + ''''
print '''' + coalesce(null + 'Any','Results in null') + ''''
print '''' + coalesce(null + 'A','Results in null') + ''''
print '''' + coalesce(null + '','Results in null') + ''''
/*
'Results in null'
'Results in null'
'Results in null'
'Results in null'
*/
source to share