SQL Find first occurrence of any character except the one specified

0 votes

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

Apr 19, 2023 in Cyber Security & Ethical Hacking by anish
• 400 points
704 views

1 answer to this question.

0 votes

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.

answered Apr 19, 2023 by Edureka
• 12,690 points

Related Questions In Cyber Security & Ethical Hacking

+1 vote
1 answer

How to find IP address of nodes in my network?

The IP address of the nodes connected ...READ MORE

answered Feb 9, 2019 in Cyber Security & Ethical Hacking by Omkar
• 69,220 points
4,950 views
0 votes
1 answer

Is it possible to find technolgy name of a web application using session tokens?

If the web application uses web servers that ...READ MORE

answered Aug 22, 2019 in Cyber Security & Ethical Hacking by Kumar

edited Oct 7, 2021 by Sarfaraz 772 views
0 votes
1 answer

What are the five steps of ethical hacking?

The 5 major steps involved in ethical ...READ MORE

answered Jan 31, 2020 in Cyber Security & Ethical Hacking by Sirajul
• 59,230 points

edited Oct 6, 2021 by Sarfaraz 6,879 views
0 votes
2 answers

does kali linux provide all services of ethical hacking in one application

Hey, @Vatsal, Almost all the services of Ethical ...READ MORE

answered Apr 21, 2020 in Cyber Security & Ethical Hacking by Gitika
• 65,770 points

edited Oct 6, 2021 by Sarfaraz 798 views
0 votes
0 answers

UPDATE and REPLACE part of a string

A table I have has two columns: ...READ MORE

Aug 28, 2022 in Database by Kithuzzz
• 38,000 points
1,323 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
801 views
0 votes
1 answer

splitting and concatenating a string

You can try the following code in ...READ MORE

answered Apr 17, 2019 in Python by SDeb
• 13,300 points
988 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,070 points
828 views
0 votes
1 answer

how to find host name from IP with out login to the host

To find host name from IP you ...READ MORE

answered Feb 20, 2022 in Cyber Security & Ethical Hacking by Edureka
• 12,690 points
88,002 views
0 votes
1 answer

Can I determine the current IP from a known MAC Address?

ARP may be used to retrieve an ...READ MORE

answered Feb 20, 2022 in Cyber Security & Ethical Hacking by Edureka
• 12,690 points
636 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP