SQL: combining multiple fields if values are present

To join multiple fields, I can do:

Select FirstName || " " || LastName || ", " || Title || " (" || Role || ")" as FullDesc...

Open in new window

The problem of course is that if there is no Title or Role (either null values or simply empty string), I don't want to have a trailing commas or empty paren: I only want the comma after the last name if there is a title, and only want the paren if there is a role. Results like this would be unacceptable:

Bilbo Baggins,  ()

Open in new window

So, how do I append pieces to the result only if there is some value in the field, and skip the field (and the corresponding punctuation or whatever I needed to add) if there isn't? Thanks.
Databases are Firebird 2.0/2.5, Interbase XE, and a few lingering IB6 (which should be gone shortly).

DatabasesSQL

Last Comment

EricTaylor

8/22/2022 - Mon

David Kroll

5/21/2012

select coalesce(FirstName + ' ', '') + coalesce(LastName, '') + coalesce(' ,' + Title, '') + coalesce(' (' + Role + ')', '') as FullDescription

EricTaylor

5/21/2012

ASKER

dkrollCTN: This nicely addresses the part of the question where the value is null, but doesn't address the situation where the field is not null but simply contains and empty string, i.e. "" instead of <null>.

Also, just for the record in case someone else reads this in the future, using + to put strings together doesn't work in firebird; need ||; e.g. coalesce(", " || Title, "")

David Kroll

5/21/2012

Not sure if this will work with Firebird, but try this:

coalesce(', ' || NULLIF(Title, ''), '')

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

EricTaylor

5/21/2012

ASKER

Thanks. That does work with firebird. You've given me enough for that to count as a solution to my question as posed, but let me see if I can get clarification on one more variation here. I have fields where some values are null, some have empty strings, and some have values of "None". Can one piece together something that filters out both "" and "None"? I've attempted "case where..." but can't seem to get that to work in the midst of ||, but I'm not sure if it can't be done or if there was something wrong with my syntax.

ASKER CERTIFIED SOLUTION

David Kroll

5/21/2012

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

EricTaylor

5/21/2012

ASKER

Thanks. Thought I tried that, but must have messed something up, because that works. Appreciate the help. :)