How to concatenate strings and commas in SQL Server?

Question

I'm relatively new to MSSQL, so sorry if the question might sounds trivial. I want to concatenate multiple fields with a delimiter ,. However, when the field is empty, the extra , will be included in the result string as well. So is there an easy way to solve this problem? For example,

SELECT VRI.Street_Number_and_Modifier + ',' + 
       VRI.Street_Direction + ',' + 
       VRI.Street_Name + ',' + 
       VRI.Street_Direction + ',' + 
       VRI.Street_Suffix + ',' + 
       VRI.Street_Post_Direction + ',' + 
       VRI.Unit
FROM View_Report_Information_Tables VRI

Cade Roux · Answer 1 · 2015-09-23T14:58:14.003

13

This modified version of Lamak's handles NULL or strings containing only space/empty:

SELECT  COALESCE(NULLIF(VRI.Street_Number_and_Modifier, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Direction, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Name, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Direction, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Suffix, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Post_Direction, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Unit, ''), '')
FROM View_Report_Information_Tables VRI

edited Sep 23 '15 at 14:58

answered Mar 18 '11 at 17:34

Cade Roux

88,164
40
182
265

Nice - I didn't know about NULLIF. Thanks! – Joel Beckham Mar 18 '11 at 17:37
This example doesn't work. COALESCE function requires at least 2 parameters. – marisks Sep 23 '15 at 11:39

score 10 · Answer 2 · answered Oct 21 '11 at 18:17

I was able to get it to work with a slightly different approach. Putting the commas at the beginning of each field and then removing the first one with the STUFF function worked for me:

SELECT 

    STUFF((COALESCE(', ' + NULLIF(VRI.Street_Number_and_Modifier, ''), '') +
        COALESCE(', ' + NULLIF(VRI.Street_Direction, ''), '') + 
        COALESCE(', ' + NULLIF(VRI.Street_Name, ''), '')) +
        COALESCE(', ' + NULLIF(VRI.Street_Direction, ''), '')) +
        COALESCE(', ' + NULLIF(VRI.Street_Suffix, ''), '')) +
        COALESCE(', ' + NULLIF(VRI.Street_Post_Direction, ''), '')) +
        COALESCE(', ' + NULLIF(VRI.Unit, ''), ''))
    , 1, 2, '')

FROM View_Report_Information_Tables AS VRI

The brackets are a bit all-over-the-place, but this works well! — Moo, Apr 03 '14 at 16:36

score 6 · Accepted Answer · answered Mar 18 '11 at 17:25

6

If the columns are empty instead of null, you can try this:

SELECT VRI.Street_Number_and_Modifier 
    + CASE WHEN VRI.Street_Number_and_Modifier <> '' THEN ', ' ELSE '' END
       + VRI.Street_Direction
    + CASE WHEN VRI.Street_Direction <> '' THEN ', ' ELSE '' END
       + VRI.Street_Name
    + CASE WHEN VRI.Street_Name <> '' THEN ', ' ELSE '' END
       + VRI.Street_Direction
    + CASE WHEN VRI.Street_Direction <> '' THEN ', ' ELSE '' END
       + VRI.Street_Suffix
    + CASE WHEN VRI.Street_Suffix <> '' THEN ', ' ELSE '' END
       + VRI.Street_Post_Direction
    + CASE WHEN VRI.Street_Post_Direction <> '' THEN ', ' ELSE '' END
       + VRI.Unit
    + CASE WHEN VRI.Unit<> '' THEN ', ' ELSE '' END
FROM View_Report_Information_Tables VRI

answered Mar 18 '11 at 17:25

Joel Beckham

18,254
3
35
58

Thanks, but now I got syntax error: `Incorrect syntax near '.'` Any idea? – roxrook Mar 18 '11 at 17:27
@Chan - Looking into what it might be. Also, I made a bunch of other mistakes that i'll fix really fast. – Joel Beckham Mar 18 '11 at 17:28
Now, it worked perfectly. Thanks for such an elegant solution ;). – roxrook Mar 18 '11 at 17:30
1

@Chan - Just keep in mind that this only works if the fields are empty strings. If their values could be null, you'll definitely want to look into combining this with some of the other solutions. – Joel Beckham Mar 18 '11 at 17:34

score 4 · Answer 4 · answered Jan 14 '19 at 10:57

For SQL 2008+

Using ISNULL(Colmn1 + ', ', '') Will always result with a leading comma in the end, so you'll have to handle it. Example:

DECLARE @Column1 NVARCHAR(10) = 'Column1'
,   @Column2 NVARCHAR(10) = 'Column2'

SELECT  SUBSTRING(  ISNULL(@Column1 + ', ', '') + ISNULL(@Column2 + ', ', '')
            , 0 --Starting from 0 not 1 to remove leading comma
            , LEN(ISNULL(@Column1 + ', ', '') + ISNULL(@Column2 + ', ', '')))

Or we could approach this the other way around and use the STUFF function to remove our beginning comma which looks cleaner, example:

SELECT  STUFF   (ISNULL(( ', ' + @Column1), '') + ISNULL(( ', ' + @Column2), ''), 1, 2, N'')

For SQL 2012+ we could use the CONCAT function and remove beginning comma using STUFF similar to our previous example but avoiding ISNULL:

SELECT  STUFF(CONCAT( ', ' + @Column1, ', ' + @Column2), 1, 2, N'')

For SQL 2017+ CONCAT_WS was introduced where you can concatinate/join multiple string columns with a delimiter specified in the first argument of the function:

MS Documents CONCAT_WS

MS Doc Example:

SELECT CONCAT_WS(',' --delimiter
            ,'1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;

score 2 · Answer 5 · answered Mar 18 '11 at 17:12

2

Try this:

SELECT  COALESCE(VRI.Street_Number_and_Modifier + ',','') + 
        COALESCE(VRI.Street_Direction + ',','') + 
        COALESCE(VRI.Street_Name + ',','') + 
        COALESCE(VRI.Street_Direction + ',','') + 
        COALESCE(VRI.Street_Suffix + ',','') + 
        COALESCE(VRI.Street_Post_Direction + ',','') + 
        COALESCE(VRI.Unit,'')
FROM View_Report_Information_Tables VRI

answered Mar 18 '11 at 17:12

Lamak

69,480
12
108
116

@Lamak: Many thanks, very clean and easy to understand. But the commas are still there :(. Any idea? – roxrook Mar 18 '11 at 17:14
@Chan: What do you mean with "the commas are still there"?, can you show an example of the input and the output?. The query above should work that when one field is `NULL`, you shouldn't get any extra commas. – Lamak Mar 18 '11 at 17:17
@Lamak: This is the result that I got: `,,Mae Rose,,Dr,,` – roxrook Mar 18 '11 at 17:18
@Chan: I believe that your problem is that you are not getting `NULL` when the field is empty, you are getting a ''. – Lamak Mar 18 '11 at 17:21
@Lamak: So how can I deal with this issue? Thanks. – roxrook Mar 18 '11 at 17:23
@Quandary: I'm running SQL Server 2008 R. – roxrook Mar 18 '11 at 17:24

score 2 · Answer 6 · answered Mar 18 '11 at 17:14

2

Short or long answer?

Short answer - dont. This is a formatting issue, not a database issue.

Long answer - When you concatenate a string and a null in sql server, the result is null. So you can use combinations of ISNULL

SELECT ISNULL(afield + ',','') + ISNULL(bfield + ',','')

answered Mar 18 '11 at 17:14

Jamiec

133,658
13
134
193

Will never be null because you have added a comma to null. – freegnu Mar 18 '11 at 17:56
1

@freegnu - SQL Server from 2008 calls a null + a string a null. – Jamiec Mar 21 '11 at 08:51
1

I tried this for MSSQL 2008 and it worked as I wanted. I wanted to concat lastname + firstname + initial. In this I wanted to insert a comma ', ' after lastname only if it is not null. Thanks for the info Jamlec – Code Buster Apr 14 '15 at 13:38

Stefan Steiger · Answer 7 · 2011-03-23T11:20:13.673

You have to use select case when IsNull(fieldname, '')= '' or ltrim(rtrim(fieldname))='') Then ... Else... end +...

Edit:
Was written from Android mobile.
Below your example.
The following translations (from German) apply, FYI:

Vorname: given name
Name: surname
Benutzer: User

And here's the example code:

CREATE VIEW [dbo].[V_RPT_SEL_Benutzer]  
AS  
SELECT 

    BE_ID AS RPT_UID, 

    CASE 
        WHEN (ISNULL(BE_Name, '0') = '0' OR LTRIM(RTRIM(BE_Name)) = '')  AND (ISNULL(BE_Vorname, '0') = '0' OR LTRIM(RTRIM(BE_Vorname)) = '')
            THEN ''
        WHEN (ISNULL(BE_Name, '0') = '0' OR LTRIM(RTRIM(BE_Name)) = '')
            THEN ISNULL(BE_Vorname, '') 
        WHEN (ISNULL(BE_Vorname, '0') = '0' OR LTRIM(RTRIM(BE_Vorname)) = '')
            THEN ISNULL(BE_Name, '') 
        ELSE
            ISNULL(BE_Name, '') + ', ' + ISNULL(BE_Vorname, '') 
    END AS RPT_Name, 

    ROW_NUMBER() OVER (ORDER BY BE_Name, BE_Vorname ASC) AS RPT_Sort 

FROM T_Benutzer

@Chan: Example added. @Cade Roux: Is this still true for SQL 2008? I remember needing it... Well ISNULL(BE_Name, '') = '' should suffice then, so forget all the or statements, but only the or, not the and. — Stefan Steiger, Mar 23 '11 at 11:23

score 0 · Answer 8 · answered Jul 20 '15 at 07:53

SELECT  COALESCE(NULLIF(ad.UDEFPROPERTYADDRESS_ADDRSS_LN_1_TXT, ''), ',')+
    COALESCE(NULLIF(ad.UDEFPROPERTYADDRESS_ADDRSS_LN_2_TXT, '') , ',')+
    COALESCE(NULLIF(ad.UDEFPROPERTYADDRESS_ADDRSS_LN_3_TXT, '') , ',')+
    COALESCE(NULLIF(ad.UDEFPROPERTYADDRESS_CITY_TXT, '') , ',')+
    COALESCE(NULLIF(ad.UDEFPROPERTYADDRESS_ST_TXT, '') , ',')+
    COALESCE(NULLIF(ad.UDEFPROPERTYADDRESS_CNTRY_TXT, '') , ',')+
    COALESCE(NULLIF(ad.UDEFPROPERTYADDRESS_PSTL_CD, '') , '')
    FROM ACCOUNT_DETAILS ad

score 0 · Answer 9 · edited Jul 10 '16 at 16:45

0

This will not add any commas if null-strings

SELECT CONCAT_WS(', ', IFNULL(column1, NULL), 
         IFNULL(column2, NULL), IFNULL(column3, NULL), 
         IFNULL(column4, NULL), IFNULL(column5, NULL))
FROM yourtable

edited Jul 10 '16 at 16:45

ρяσѕρєя K

132,198
53
198
213

answered Jul 10 '16 at 16:17

Oleg

187
3
5

Downvote, sqlserver does not have the function CONCAT_WS. That is a MYSQL function. – coding Bott Jan 12 '17 at 16:48

score 0 · Answer 10 · answered Mar 18 '11 at 17:13

0

You could use the ISNULL(field + ',', '')

answered Mar 18 '11 at 17:13

Andrew

5,215
1
23
42

Thanks, however the commas are still there :(. Any idea? – roxrook Mar 18 '11 at 17:17
Will never be null because you have added a comma to null. – freegnu Mar 18 '11 at 17:56
@freegnu try this select isnull(null + ',',''). Null + 'a string' returns null – Andrew Mar 18 '11 at 19:56

score 0 · Answer 11 · answered Mar 18 '11 at 17:17

0

SELECT isnull(VRI.Street_Number_and_Modifier + ',','')+
   isnull(VRI.Street_Direction + ',','')+
   isnull(VRI.Street_Name + ',','')+
   isnull(VRI.Street_Direction + ',','')+
   isnull(VRI.Street_Suffix + ',','')+
   isnull(VRI.Street_Post_Direction + ',','')+
   isnull(VRI.Unit,'')
FROM View_Report_Information_Tables VRI

answered Mar 18 '11 at 17:17

Dumitrescu Bogdan

7,127
2
23
31

Doesn't work, because the comma also needs to be removed when string2 = null – Stefan Steiger Mar 18 '11 at 17:26
The fields can also be string.empty or only whitespaces. Additionally, this only works on ms-sql 2008 where string + null =null, unlike sql 2005. – Stefan Steiger Mar 18 '11 at 17:29
@Quandary string + null = null on SQL Server 2005 if you have ANSI_NULLs ON (which it should be by default, even on SQL Server 2005). – Cade Roux Mar 18 '11 at 17:58

score 0 · Answer 12 · answered Mar 18 '11 at 17:20

I would agree completely with Jamiec's short answer.

Otherwise, I would look at a nasty solution of using a REPLACE([concat], ',,', ',') everywhere you concatenate two columns, and then figure out how to trim commas from the beginning and end of the string where the first and last columns might be empty. Very very messy.

AhmedHuq · Answer 13 · 2011-12-09T11:50:29.917

Wanted to see if I can get it without using CASE but could not. A long-winded way of mine:

    SELECT case when isnull(nullif(VRI.Street_Number_and_Modifier, ''),'')='' then '' else VRI.Street_Number_and_Modifier end
+ case when isnull(nullif(VRI.Street_Direction, ''),'')='' then '' else ',' + VRI.Street_Direction end
+ case when isnull(nullif(VRI.Street_Name, ''),'')='' then '' else ',' + VRI.Street_Name end
+ case when isnull(nullif(VRI.Street_Suffix, ''),'')='' then '' else ',' + VRI.Street_Suffix end
+ case when isnull(nullif(VRI.Street_Post_Direction, ''),'')='' then '' else ',' + VRI.Street_Post_Direction end
+ case when isnull(nullif(VRI.Street_Post_Direction, ''),'')='' then '' else ',' + VRI.Street_Post_Direction end
FROM View_Report_Information_Tables VRI

How to concatenate strings and commas in SQL Server?

13 Answers13