SQL Server controlling spaces when concatenating

Question

This seems a simple question, but it is something I always seem to be up against.

When concatenating two (or more) fields in SQL Server with adding a space between fields like:

SELECT Field1 + ' ' + Field2 FROM table

If Field1 or Field2 may be null, So we can use ISNULL to convert it to an empty string but the space remains. One way I have got around this is to add the space before checking if it is null. And then trimming the ends to allow for empty strings in the fields. E.g:

SELECT LTRIM(RTRIIM(ISNULL(Field1 + ' ','') + ISNULL(Field2, ''))) FROM Table

This handles empty strings in either of the fields but gets very long if there is more than 2 fields to be concatenated in this way.

Is there an easier way?

shA.t · Accepted Answer · 2016-11-05T07:19:35.227

3

An easier way:

SELECT COALESCE(Field1 + ' ' + Field2, Field1, Field2, '')
FROM Table

This also avoid changes of LTRIM and RTRIM over fields.

But for more fields I use:

SELECT SUBSTRING(ISNULL(' ' + Field1, '') + ISNULL(' ' + Field2, '')
                                    + ISNULL(' ' + Field3, '') 
                                    + ISNULL(' ' + Field4, '') 
                                    + ...
                                    + ISNULL(' ' + FieldN, ''), 2, 4000)
FROM Table

edited Nov 05 '16 at 07:19

answered May 12 '15 at 03:40

shA.t

16,580
5
54
111

Thanks @shA.t exactly the answer I was looking for. – Brad May 12 '15 at 04:32
With this approach, if Field1 is NOT NULL, the string will start in the 2nd characterd of Field1 which is not correct – Yván Ecarri Nov 04 '16 at 16:42

score 0 · Answer 2 · edited May 23 '17 at 11:46

I found Aaron Bertrands answer very helpful in doing something similar with a combination of NULLS and/or empty fields. For my solution, I ended up with;

SELECT STUFF(COALESCE(' ' + NULLIF(RTRIM(LTRIM(a.address_line1)), ''), '') + 
          COALESCE(' ' + NULLIF(RTRIM(LTRIM(a.address_line2)), ''), '') + 
          COALESCE(' ' + NULLIF(RTRIM(LTRIM(a.address_line3)), ''), '') +
          COALESCE(' ' + NULLIF(RTRIM(LTRIM(a.address_suburb)), ''), '') +
          COALESCE(' ' + NULLIF(RTRIM(LTRIM(a.address_state)), ''), '') +
          COALESCE(' ' + NULLIF(RTRIM(LTRIM(a.address_postcode)), ''), ''), 1, 1, '') 
          AS Address_Detail

This may well not be an "easier way", but it might help you with some alternative ideas.

SQL Server controlling spaces when concatenating

2 Answers2