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:
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).
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, "")
Not sure if this will work with Firebird, but try this:
coalesce(', ' || NULLIF(Title, ''), '')
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!
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.
Thanks. Thought I tried that, but must have messed something up, because that works. Appreciate the help. :)
Not the solution you were looking for?
IT issues often require a personalized solution. With Ask the Experts™, submit your questions to our certified professionals and receive unlimited, customized solutions that work for you.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial