asked on

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).

David Kroll

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

EricTaylor

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

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

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

EricTaylor

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EricTaylor

ASKER

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