SQL Server controlling spaces when concatenating -

this seems simple question, seem against.

when concatenating 2 (or more) fields in sql server adding space between fields like:

select field1 + ' ' + field2 table 

if field1 or field2 may null, can use isnull convert empty string space remains. 1 way have got around add space before checking if null. , trimming ends allow empty strings in fields. e.g:

select ltrim(rtriim(isnull(field1 + ' ','') + isnull(field2, ''))) table 

this handles empty strings in either of fields gets long if there more 2 fields concatenated in way.

is there easier way?

an easier way:

select coalesce(field1 + ' ' + field2, field1, field2, '') table 

this avoid changes of ltrim , rtrim on fields.

---

but more fields use:

select substring(isnull(' ' + field1, '') + isnull(' ' + field2, '')                                     + isnull(' ' + field3, '')                                      + isnull(' ' + field4, '')                                      + ...                                     + isnull(' ' + fieldn, ''), 2, 4000) table