Conditionals in transact-sql select column lists

Question

I've got a query that looks a bit like this:

select
records.id,
contacts.name + ' (' + contacts.organization + ')' as contact,
from records
left join contacts on records.contact = contacts.contactid

Problem is - contacts.organization is frequently empty, and I get contacts like "John Smith ()". Is there a way to only concatenate the organization if it's non-empty?

Answer 1

Use a CASE statement

SELECT
records.id,
CASE contacts.organization
    WHEN '' THEN contacts.name
    ELSE contacts.name + ' (' + contacts.organization + ')'
END as Contact
FROM records
LEFT JOIN contacts ON records.contact = contacts.contactid

You could modify it to also check for NULL values, but I do not believe you have that issue because if you had a NULL in your contacts.organization, your entire result field would be null instead of blank.

Answer 2

Not sure if this is the best way to do it:

CASE contacts.organization
WHEN '' THEN ''
ELSE '(' + contacts.organzation + ')' END

Answer 3

use a CASE, like CASE WHEN contacts.organization not null then ' (' + c.o + ') ' else '' end

Answer 4

You always need to expect nulls, because of your outer join:

select
records.id,
contacts.name + CASE WHEN contacts.organization IS NULL OR contacts.organization='' THEN '' ELSE ' (' + contacts.organization + ')' END as contact,
from records
left join contacts on records.contact = contacts.contactid

Answer 5

If you're dealing with NULL values there are some functions that specialize in them which are worth knowing.

• ISNULL()
• COALESCE()
• NULLIF()

NULLIF() might be the one you're looking for. It basically takes two params. It returns the first param unless it is NULL, otherwise it returns the second.

Here's what I approximate your code would be:

select
records.id,
contacts.name + ISNULL(' (' + contacts.organization + ')', '') as contact,
from records
left join contacts on records.contact = contacts.contactid

Most NULL-related functions can be replaced by a larger CASE statement. CASE is your more general tool. But using specific functions will make your code cleaner, or at least more terse.