CODEWITHSUNDEEP
sql-serverstringsubstringcharindex
syebill
syebill

Reputation: 543

Extracting sub string from a string in sql sever

I have the following string values in a column within a SQL server database table:

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

I am trying to retrieve values only when it is preceded by 'CS' but there are multiple cases as shown in above examples. 'CS' may appear on it's own or with number separated by '-' and then a color separated by a space. It can be in the beginning of a string, in the middle or at the end. I want to first check if there is only one value in the column by checking for a comma, if there is only one value and it contains the word 'CS' then it looks simple. But when there are multiple values on either side of the string interested in then it becomes a problem.

I will appreciate any suggestions or solutions in sql server please.

The result should look like: 

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

Thanks.

Upvotes: 2

Views: 71

Answers (3)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Interesting business requirement, test below script this will almost solve your problem.

SELECT CASE WHEN CHARINDEX(',',ColName) = 0 THEN ColName
        WHEN CHARINDEX('CS',ColName) < CHARINDEX(',',ColName) THEN LEFT(ColName,CHARINDEX(',',ColName)-1) 
        WHEN CHARINDEX('CS',ColName) > CHARINDEX(',',ColName) THEN PARSENAME(REPLACE(ColName,',','.'),2) 
        END
From TableName

Upvotes: 1

Holger Brandt
Holger Brandt

Reputation: 4354

You can do it in 2 passes. The first looks for the 'CS-' or 'CS'. The second looks for the comma and only returns the text before it (or the whole string if there isn't any comma).

SELECT CASE WHEN CHARINDEX(',',Result1)>0 THEN LEFT(Result1,CHARINDEX(',',Result1)-1)
            ELSE Result1
       END AS FinalResult
FROM 
  (SELECT CASE WHEN CHARINDEX('CS-',[Orig_String])>0 THEN SUBSTRING([Orig_String],CHARINDEX('CS-',[Orig_String]), LEN([Orig_String]))
               ELSE [Orig_String]          
          END AS Result1
  FROM [Table1]) AS T

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

You can use CHARINDEX + LEFT string function

CHARINDEX - Helps you to identify the first occurrence of the comma & CS in the string

SUBSTRING - Extract the string from CS to first occurrence of the comma in the string

SELECT org_string, 
       Result_string = substring(org_string,pos,CASE WHEN Charindex(',', org_string,pos+1) <> 0 then Charindex(',', org_string,pos+1)-pos else len(org_string) end)
FROM   ( VALUES ('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')) cs (org_string) 
                cross apply (values (charindex('CS',org_string))) p (pos)

Result :

╔══════════════════════════════════╦════════════════════╗
║            org_string            ║   Result_string    ║
╠══════════════════════════════════╬════════════════════╣
║ CS-NO2-T6082 BROWN,STORAGE       ║ CS-NO2-T6082 BROWN ║
║ CS-NO2-T6082 BROWN               ║ CS-NO2-T6082 BROWN ║
║ CS-CT2N64045,POW REC X 2,ROCKER  ║ CS-CT2N64045       ║
║ CONSOLE,CS-CT2N74045,POW REC X 2 ║ CS-CT2N74045       ║
║ CS                               ║ CS                 ║
║ CS,MAN REC                       ║ CS                 ║
║ CS-T6082,POW REC X 2             ║ CS-T6082           ║
║ CS-CT12N1176                     ║ CS-CT12N1176       ║
║ CS-NO2-T6082 BROWN,SQUARE        ║ CS-NO2-T6082 BROWN ║
║ CS-CT12N1176                     ║ CS-CT12N1176       ║
║ CS-2T1176 GREY                   ║ CS-2T1176 GREY     ║
╚══════════════════════════════════╩════════════════════╝

Upvotes: 1

Related Questions