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?
 
dqmqConnect With a Mentor Commented:
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;
0
 
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?!
0
 
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;
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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;
0
 
HainKurtSr. System AnalystCommented:
try something like this:

case when isnull(lastname,'')+', '+isnull(firstname,'')=', ' then companyname else isnull(lastname,'')+', '+isnull(firstname,'') end as name
0
 
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
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.