tsql - SQL Server : concat two columns with delimiter if both not nulll else return third column

Question

Here's a sample table

FirstName   LastName   UserName
----------------------------------
Bob         Jones      bjones
null        null       tomroberts
null        Ricardo    rricardo
Robby       null       robroy
George      Glass      gglass

I'm looking for a select that'll return

Jones, Bob
tomroberts
rricardo
robroy
Glass, George

If the first name OR last name are null or empty strings, I want to return the user name. Otherwise I want to return the "lastname, firstname"

Can someone help with the query?

Thanks!

question from:https://stackoverflow.com/questions/66047403/sql-server-concat-two-columns-with-delimiter-if-both-not-nulll-else-return-thi

Answer 1

An alternative to using a CASE statement is to use the COALESCE function which returns the first non-NULL argument.

SELECT 
    COALESCE(NULLIF(LastName,'') + ',' + NULLIF(FirstName,''), UserName)
FROM 
  MyTable

Note:- It's important to use the + to concat the columns as this will return NULL if any column contains null. The CONCAT function will return a value that contains the non-NULL parts.