Can we concat as well as replace string values in SQL Server (SSMS)?

I executed below query, but I got result as null values.

SELECT CAST(REPLACE(REPLACE(REPLACE(REPLACE(FIRST_NAME,'null',''),'N/A',''),'no',''),'NA','') +' ' + REPLACE(REPLACE(REPLACE(REPLACE(LAST_NAME,'null',''),'N/A',''),'no',''),'NA','')) AS 'FULL_NAME' FROM NAMES

Instead I want my output to be:

FULL_NAME
___________
Ankit Kumar
Mayank Sharma
Puneet Kumar
Rajesh
Narender
Robert
Chahuhan
Jaswinder Singh
Rakesh

Answer

The more recent versions of SQL Server support concat_ws():

select concat_ws(' ', 
                 (case when first_name not in ('na', 'no', 'null') then first_name end),
                 (case when last_name not in ('na', 'no', 'null') then last_name end)
                ) as full_name

In older versions, you can use:

select ltrim( (case when first_name not in ('na', 'no', 'null') then ' ' + first_name else '' end) +
              (case when last_name not in ('na', 'no', 'null') then ' ' + last_name else '' end)
            ) as full_name