Posted by Erland Sommarskog on 07/28/06 21:25

Sharif Islam (mislam@spam.uiuc.edu) writes:
> I used SET CONCAT_NULL_YIELDS_NULL OFF do ignore NULL in concatation.

That setting is only for legacy software and there is no reason to use
it new code.

> Here's my query:
> SELECT ([chapterauthor] + [authors]) AS CT FROM Items ORDER BY CT

SELECT coalesce(chapterauthor, '') + coalesce(authors, '') AS CT
FROM Items

coalesce() is a function that accept a list of values as parameters and
returns the first non-NULL value in the list.

> Both Authors and ChapterAuthor can be null. I am trying to figure out if
> I can ignore the null value when ChapterAuthor is null.
>
> Sample data/result:
> Appadurai, Arjun #chapter author is null
> Appadurai, ArjunFardon, Richard (ed.) # (chapterauthor+authors)
>
> I would like the output to be like this:
> Appadurai, ArjunFardon, Richard (ed.) # (chapterauthor+authors)
> Appadurai, Arjun #chapter author is null
>
> I guess I can write a stored procedure with if/else, but I was wondering
> If I can do this with a simple query. Thanks.

I am not sure that I understand exactly what you are looking for, but
try:

ORDER BY CASE WHEN chapterauthor IS NOT NULL THEN 1 ELSE 0 END, CT


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]