sql - Strange behavior with ISNULL() in MSSQL -

i have select statement combining multiple segments of persons name. isn't new.

select firstname + ' ' + lastname fullname mytable 

i tried add middle initial , came following

select firstname + ' ' + isnull(middinginitial + ' ', '') + lastname fullname mytable 

this appears work, during testing of isnull(), came across odd behavior. i'm aware null + 'any string' resolves null. plain odd...

here's code, , out 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 of why behavior occurs? same you?

it comes down datatypes you're working , behavior of isnull function. let's @ 1 example:

null + 'any string' 

the above fits varchar(11) datatype. null (which result of char(0) , has length 1) , regular 10-character string concatenated makes 11 characters total. replacement string -- second parameter isnull function -- going forced fit varchar(11), truncated 11 characters.

the pattern repeats remaining items, special case empty string.

if don't want happen, use coalesce, instead of taking datatype of first item in list, uses data type precedence. varchar(15) takes precedence on varchar(11), full 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' */