I used SET CONCAT_NULL_YIELDS_NULL OFF do ignore NULL in concatation. I
have two fields:
chapterauthor
authors

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


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

Sharif Islam (mislam@spam.uiuc.edu) writes:
[quoted text, click to view]

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

[quoted text, click to view]

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.

[quoted text, click to view]

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