Posted by Erland Sommarskog on 07/30/05 01:22

NickName (dadada@rock.com) writes:
> -- option 1
> select (IsNull(first_name,'') + ' ' + Case Len(middle_name) when 0 then
> '' else IsNull((middle_name + ' '),'') end + IsNull(last_name,'')) as
> name
> from #cat
>
> -- option 2
> select (IsNull(first_name,'') + ' ' +
> IsNull(NullIf(Coalesce((middle_name + ' '),''),''),'') +
> IsNull(last_name,'')) as name
> from #cat
>
> q:
> both option 1 and option 2 produces same result, which one is more
> desirable?

Matter of taste, I guess. This is my favourite, although it gives a
slightly different result:

select CASE WHEN len(first_name) > 0 THEN first_name + ' ' ELSE '' END +
CASE WHEN len(middle_name) > 0 THEN middle_name + ' ' ELSE '' END +
CASE WHEN len(last_name) > 0 THEN last_name ELSE '' END
name
from #cat



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]