CODEWITHSUNDEEP
sqlsql-serversubstring
R B
R B

Reputation: 423

SQL substring from a string

I have a table XYZ with column FileName which has values as follows: 

CCA_Type-PROPOSAL_Id-45845_Test1.txt
CPA_Type-PROPOSAL_Id-490845_Test2.txt

I want to update this column so that it contains only the filename and remove other characters preceeding:

Test1.txt
Test2.txt

Hence I wrote the following:

Update XYZ
set FileName = (select RIGHT(FileName,CHARINDEX('_',REVERSE(FileName),0)-1))

But if a FileName has a value like:

  CCA_Type-PROPOSAL_Id-45845_Test_RR1.txt

My script returns RR1.txt instead of Test_RR1.txt! It finds the last underscore and returns substring from there. How can I change it so that I get the 3rd underscore and return a substring following it!

Upvotes: 1

Views: 278

Answers (2)

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

something like this should work:

declare @table table ( 
  [file_name] [sysname] 
  ); 
declare @pattern [sysname]= N'_test'; 
insert into @table 
            ([file_name]) 
values      (N'CCA_Type-PROPOSAL_Id-45845_Test1.txt'), 
            (N'CPA_Type-PROPOSAL_Id-490845_Test2.txt'), 
            (N'CCA_Type-PROPOSAL_Id-45845_Test_RR1.txt'); 
select [file_name]                                                                         as [file_name] 
       , charindex(@pattern, lower([file_name]))                                           as [character_index_of_pattern]
       , substring([file_name], charindex(@pattern, lower([file_name])), len([file_name])) as [desired_output] 
from   @table;

Upvotes: 1

Blorgbeard
Blorgbeard

Reputation: 103447

charindex optionally takes a start location. You could chain several together:

select right(FileName, len(FileName) - 
   charindex('_', FileName, 
      charindex('_', FileName, 
         charindex('_', FileName) 
      + 1) 
   + 1))

So you're asking for "The first underscore after (the first underscore after (the first underscore))" - i.e. the third underscore.

Upvotes: 3

Related Questions