Geek Answers Handbook

How to concatenate strings and commas in SQL Server?

I'm relatively new to MSSQL, so sorry if the question may seem trivial. I want to combine multiple fields with a separator,. However, when the field is empty, the incremental will also be included in the resulting string. 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
+7
source share
12 answers

If the columns are empty rather than null, you can try the following:

 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
+6
source

This modified version of Lamak handles NULL or lines 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
+11
source

I managed to get it to work with a slightly different approach. Putting commas at the beginning of each field and then deleting the first with the STUFF function working 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
+7
source

Try the following:

 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
+2
source

Short or long answer?

The short answer is no. This is a formatting issue, not a database issue.

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

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

You should use the selection case when IsNull (field name, ``) = '' or ltrim (rtrim (field name)) = '') Then ... Else ... end + ...

Edit:
It was written with Android mobile.
Below is your example.
The following translations apply (from German), FYI:

Vorname: given name
First Name Last Name
Benutzer: User

And here is a sample 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
+1
source

You can use ISNULL(field + ',', '')

0
source 
 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
0
source

I would completely agree with Jamek's short answer.

Otherwise, I would look at the unpleasant decision to use REPLACE ([concat], ',', ',') wherever you combine two columns, and then figure out how to trim commas from the beginning to the end of the line in which the first and last columns may be empty. Very very dirty.

0
source

I wanted to see if I could get it without using CASE, but I couldn’t. My long way:

  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
0
source 
 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
0
source

This will not add any commas if the null lines

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

More articles:


All Articles