How to select multiple columns and return the one which is not empty

I'm working on a SQL query for an application and there are 3 fields, first, last and company name.  If the first and last name are empty then I want to return the company name and if the first and last name have data, I want to return that information.  I was originally using the following statement:

SELECT c.ID, coalesce (c.LastName + ', ' + c.FirstName, c.Company) AS Name
FROM Customer as c
ORDER BY LastName

I then realized when I commit data to the database from my application which is written in C#, it's not saving a Null value to the database when a field is blank so coalesce does not work for my purpose.  Is there a similar way to achieve the above query that works on empty strings?

Thanks
obb-taurusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barletCommented:
try to convert <empty_fields> to NULL with the following command
UPDATE your_Table
set [your_Column] = NULLIF([your_Column], '')

Open in new window

and then use coalesce?!
obb-taurusAuthor Commented:
Yes, that would work but if the data from my application but if the data is not being saved as NULl, I will have to run this update every time data is saved from my application which is not very efficient.  What I have come up with is as follows, not sure if this is a good way to accomplish what I'm trying to do.

SELECT c.ID,
CASE
   WHEN c.FirstName IS NULL AND c.LastName IS NULL
         THEN c.Company
   WHEN c.FirstName = '' AND c.LastName = ''
         THEN c.Company
         ELSE
             c.FirstName + ', ' + c.LastName
END
   AS Name
FROM Customer as c
ORDER BY LastName;
dqmqCommented:
Not sure you got it quite right, i.e. what if only one of the names is an empty string.  Don't know what you want back in that case, but try this;

SELECT c.ID,
coalesce(nulllif(c.lastname+', '+c.firstname,', ')
         ,nullif(c.lastname,'')
         ,nullif(c.firstname,'')
         ,c.Company) AS Name
FROM Customer as c
ORDER BY LastName;
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HainKurtSr. System AnalystCommented:
try something like this:

case when isnull(lastname,'')+', '+isnull(firstname,'')=', ' then companyname else isnull(lastname,'')+', '+isnull(firstname,'') end as name
dqmqCommented:
Slight correction:

SELECT c.ID,
coalesce(nullif(c.lastname,'')+', '+nullif(c.firstname,'')
         ,nullif(c.lastname+c.firstname,'')
         ,c.Company) AS Name
FROM @tab as c
ORDER BY LastName;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deightonprogCommented:
SELECT c.ID, CASE WHEN LEN(coalesce (c.LastName + ', ' + c.FirstName, c.Company)) > 0 THEN coalesce (c.LastName + ', ' + c.FirstName, c.Company) ELSE c.Company END  AS Name
FROM Customer as c
ORDER BY LastName
obb-taurusAuthor Commented:
Thanks dqmq.
Also, I don't need to worry if first or last name is blank as the application doesn't allow only a first or last name to be entered, both are required.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.