How to concatenate strings and commas in SQL Server?

sql sql-server concatenation coalesce

40,925

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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;

Solution 5

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

View more solutions

40,925

Author by

roxrook

C++ is fun!

Updated on January 15, 2020

Comments

roxrook over 4 years
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
```
roxrook over 13 years

@Lamak: Many thanks, very clean and easy to understand. But the commas are still there :(. Any idea?
Lamak over 13 years

@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.
roxrook over 13 years

Thanks, however the commas are still there :(. Any idea?
roxrook over 13 years

@Lamak: This is the result that I got: ,,Mae Rose,,Dr,,
roxrook over 13 years

Can you give me one example? Thanks.
Lamak over 13 years

@Chan: I believe that your problem is that you are not getting NULL when the field is empty, you are getting a ''.
roxrook over 13 years

@Lamak: So how can I deal with this issue? Thanks.
roxrook over 13 years

@Quandary: I'm running SQL Server 2008 R.
Stefan Steiger over 13 years

Doesn't work, because the comma also needs to be removed when string2 = null
roxrook over 13 years

Thanks, but now I got syntax error: Incorrect syntax near '.' Any idea?
Joel Beckham over 13 years

@Chan - Looking into what it might be. Also, I made a bunch of other mistakes that i'll fix really fast.
Stefan Steiger over 13 years

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.
roxrook over 13 years

Now, it worked perfectly. Thanks for such an elegant solution ;).
Joel Beckham over 13 years

@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 over 13 years

Nice - I didn't know about NULLIF. Thanks!
freegnu over 13 years

Will never be null because you have added a comma to null.
freegnu over 13 years

Will never be null because you have added a comma to null.
Cade Roux over 13 years

@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).
Andrew over 13 years

@freegnu try this select isnull(null + ',',''). Null + 'a string' returns null
Jamiec over 13 years

@freegnu - SQL Server from 2008 calls a null + a string a null.
Stefan Steiger over 13 years

@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.
Moo about 10 years

The brackets are a bit all-over-the-place, but this works well!
Code Buster about 9 years

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
marisks almost 9 years

This example doesn't work. COALESCE function requires at least 2 parameters.
Miguel over 7 years

Downvote, sqlserver does not have the function CONCAT_WS. That is a MYSQL function.