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?

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;

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Featured Post

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.