SQL substring from a string

Question

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!

Search for test instead ?

Gordon Linoff
– Gordon Linoff

2014-08-25 19:45:07 +00:00
Commented Aug 25, 2014 at 19:45 — Gordon Linoff
– Gordon Linoff, Commented Aug 25, 2014 at 19:45

Blorgbeard · Accepted Answer · 2014-08-25 19:45:21Z

3

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.

answered Aug 25, 2014 at 19:45

Blorgbeard

104k50 gold badges237 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Blorgbeard · Accepted Answer · 2014-08-25 20:48:19Z

1

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;

edited Aug 25, 2014 at 20:48

Blorgbeard

104k50 gold badges237 silver badges276 bronze badges

answered Aug 25, 2014 at 19:55

Katherine Elizabeth Lightsey

8535 silver badges12 bronze badges

3 Comments

R B Over a year ago

Thanks for the reply. I found Blorgbeard's solution easier.

Blorgbeard Over a year ago

This does assume all filenames start with "test", which may not be the case.

R B Over a year ago

That is correct. All file names do not start with Test.

Collectives™ on Stack Overflow

SQL substring from a string

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related