ISNULL in SQL Server 2008 -

today have encountered odd issue isnull function in sql

i have table contains customer information have display full name concatenating first, middle initial , last name, in table know middle initial nullable column.

so used isnull function in sql return middle initial if not null, works fine used isnull function expression below ideally should not work in case if mi null, interestingly worked.

select firstname + ' ' + lastname + ' '+ isnull(mi + '.', '') middleinitial  cs_tblmaster customerno = 2627240 

above sol query should return me middleinitial "." when middleinitial null, did return empty string.

so wrote query below.

select (mi + '.') middleinitial cs_tblmaster customerno = 2627240 

which again given null

somehow when concatenate string null value returns null. understand of implementation.

can

– original statement

select firstname + ' ' + lastname + ' '+ isnull(mi + '.', '') middleinitial  cs_tblmaster customerno = 2627240 

– original answer (edited)

select firstname + ' ' + lastname + ' '+ (isnull(mi, '') + '.') middleinitial  cs_tblmaster customerno = 2627240 

in case, sql first check see if mi null , uses mi if not null or uses empty string if is. then concatenates result, never null, period.

– final answer

select      firstname + ' ' + lastname + ' '         + case when mi null '' else mi + '.' end middleinitial  cs_tblmaster customerno = 2627240 

@satish, not sure if feel have answer yet since haven't selected one, , apologize if answer short , fast. seeing responses made me realize hadn't thought question when first saw it.

to answer “i understand of implementation”, nulls special value in sql. not empty string, not spaces, not zeros. mean in more literal sense “nothing”. can check them, can see if is null. can't “do” things nulls. 57 + null = null. 'mary' + null = null. ((12 *37) +568) / null = null. max() of 'albert', 'mary', null, , 'zeke' null. article http://en.wikipedia.org/wiki/null_(sql) may help, decent description in section on null propagation.

the isnull function not test null, way handle it. test if null use columnname null or columnname not null in select. isnull(mi,'') says is: want value, if value of mi not null, want mi, otherwise want empty string.

going on, i'm not sure understood trying do. if trying period when middle initial null, original answer , of others work you. think may trying say: if have middle initial, want middle initial followed period. if don't have middle initial want nothing: 'alberto c. santaballa' or 'alberto santaballa', never 'alberto . santaballa”. if case use final statement in edited answer.

@zec, edit. typo product of too-fast typing! :-/