Do we have a workaround for the T-SQL CONCAT_WS function?

Do we have a workaround for the T-SQL CONCAT_WS function? This one throws an error that

is not a recongized built in function name

I presume this is not compatible with my version.

I am currently trying to get a proper format for my full name where MiddleName is or isn’t NULL.

Answer

First, SQL Server now supports CONCAT_WS(). So, using the built-in function is the simplest “work-around”.

As an alternative:

select stuff( (coalesce(', ' + col1, '') +
               coalesce(', ' + col2, '') +
               coalesce(', ' + col3, '')
              ), 1, 2, '') as concat_ws

The separator and “2” need to be consistent, of course.