SQL: combining multiple fields if values are present

EricTaylor
EricTaylor used Ask the Experts™
on
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).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
David Kroll

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

Author

Commented:
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

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

coalesce(', ' || NULLIF(Title, ''), '')
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
EricTaylor

Author

Commented:
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.
Commented:
coalesce(', ' || NULLIF(NULLIF(Title, ''), 'None'), '')
EricTaylor

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial