How to parse specific substring patterns starting with prefix in SQL Server

Data and Databases
1

I need help with extracting certain parts from text data stored in my SQL Server table. Each row contains different string patterns and I want to pull out only the portions that begin with ‘CS’.

Sample data in my column:

CS-NO2-T6082 BROWN,STORAGE       
CS-NO2-T6082 BROWN                      
CS-CT2N64045,POW REC X 2,ROCKER         
CONSOLE,CS-CT2N74045,POW REC X 2        
CS
CS,MAN REC   
CS-T6082,POW REC X 2                                               
CS-CT12N1176   
CS-NO2-T6082 BROWN,SQUARE  
CS-CT12N1176                                                                  
CS-2T1176 GREY

What I’m trying to achieve:
I want to extract only the segments that start with ‘CS’. The challenge is that ‘CS’ can appear alone or followed by numbers and letters separated by dashes, sometimes with colors after a space. These segments can be at the start, middle, or end of the string.

My approach is to first detect if there are multiple values by looking for commas. Single values with ‘CS’ are easy to handle, but multiple comma-separated values make it tricky.

Expected output:

CS-NO2-T6082 BROWN       
CS-NO2-T6082 BROWN                      
CS-CT2N64045        
CS-CT2N74045        
CS
CS   
CS-T6082                                               
CS-CT12N1176   
CS-NO2-T6082 BROWN  
CS-CT12N1176                                                                  
CS-2T1176 GREY

Any SQL Server solutions would be greatly appreciated.

2

The tricky part is dealing with comma-separated values when CS segments aren’t always first. I’d use STRING_SPLIT combined with CHARINDEX. Split the text by commas first, then filter for anything starting with ‘CS’: SELECT TRIM(value) FROM your_table CROSS APPLY STRING_SPLIT(column_name, ',') WHERE LTRIM(value) LIKE 'CS%'. This catches CS values buried in the middle of comma-separated strings - something PATINDEX alone would miss. You’ll need extra SUBSTRING logic for extracting colors after spaces, depending on what you’re working with.

3

Interesting challenge! What happens if CS shows up multiple times in one row? Say you’ve got “CS-ABC,OTHER-STUFF,CS-DEF” - do you want both CS parts pulled out? Also, how much data are we talking? That’ll change which approach performs best.

4

i find using PATINDEX really helpful! you can use this query: SELECT SUBSTRING(column_name, PATINDEX('CS%', column_name), LEN(column_name)) FROM your_table WHERE column_name LIKE '%CS%' it works well for those segments!