To join multiple fields, I can do:
Select FirstName || " " || LastName || ", " || Title || " (" || Role || ")" as FullDesc...
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, ()
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).