|
|
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
Navigation:
[Reply to this message]
|