Combining two column's data into one column in SQL?

Question

I am trying to combine Last name, first name and middle name into single sort name column in a SQL statement. Sometime middle name will be NULL, and if that's the case, the sort name is showing NULL.

How to handle this?

SELECT TOP 500
  Last_Name, First_Name, Middle_Name,
  [Last_Name] + ',' + [First_Name] + ' ' + [Middle_Name] AS SORT_NAME
FROM [dbo].[usr_CUSTOMER]
ORDER BY SORT_NAME

Results:

Last_Name  First_Name   MiddleName  Sort_Name
Aa         Robert       NULL        NULL

But I want to see sort_name to be 'Aa,Robert'.

Answer 1

COALESCE:

  COALESCE([Last_Name], '') + ',' + COALESCE([First_Name], '') + ' ' + 
  COALESCE( 
          [Middle_Name], '') AS SORT_NAME

Of course, this will leave ugly commas when last name or both first and middle are empty, so your actual code will need to be a bit more clever.

Answer 2

Use the ISNULL() function - it replaces a NULL value with something you define:

SELECT TOP 500 
    Last_Name, First_Name, Middle_Name, 
    [Last_Name] + ',' + [First_Name] + ' ' + ISNULL([Middle_Name], '') as SORT_NAME   
FROM [dbo].[usr_CUSTOMER]  
ORDER BY SORT_NAME