SQL Find first occurrence of any character except the one specified

Question

All,

Using SSMS v17.2

Long story short I have 15 columns in a table, each record in the table has a different number of columns filled out, the rest are null. I then concatenate the columns with a comma delimiter. Problem is the fields which are null also get concatenated leaving me with trailing commas.

ARNP,ACLS Provider,BLS Provider CPR,,,,,,,,,,,,
COC,CPC,CRC,CPC-1,,,,,,,,,,,
CISSP,CCNA Security,Network +,,,,,,,,,,,,
Leadership (All levels),Education (Grades K-12),,,,,,,,,,,,,,

As you can see the very last character before the trailing commas can be either alpha numeric or special characters. I need help removing these training commas up to the first character that is not a comma such as the below.

ARNP,ACLS Provider,BLS Provider CPR
COC,CPC,CRC,CPC-1
CISSP,CCNA Security,Network +
Leadership (All levels),Education (Grades K-12)

Thank you

Answer 1

You can use the TRIM() function to remove the trailing commas up to the first character that is not a comma. Here's an example query to achieve this:

SELECT TRIM(TRAILING ',' FROM CONCAT_WS(',', col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15)) AS concatenated_columns
FROM your_table;
 

Explanation:

• CONCAT_WS() function concatenates the non-null values of the columns with a comma delimiter.
• TRIM() function removes the trailing commas from the concatenated string.

Replace col1 to col15 with the actual column names from your table. your_table is the name of your table.

This query should return the concatenated columns without trailing commas.