title: SQL substring from a string
body: <p>I have a table XYZ with column FileName which has values as follows: </p>
  
  <pre><code>CCA_Type-PROPOSAL_Id-45845_Test1.txt
  CPA_Type-PROPOSAL_Id-490845_Test2.txt
  </code></pre>
  
  <p>I want to update this column so that it contains only the filename and remove other characters preceeding:</p>
  
  <pre><code>Test1.txt
  Test2.txt
  </code></pre>
  
  <p>Hence I wrote the following:</p>
  
  <pre><code>Update XYZ
  set FileName = (select RIGHT(FileName,CHARINDEX('_',REVERSE(FileName),0)-1))
  </code></pre>
  
  <p>But if a FileName has a value like:</p>
  
  <pre><code>  CCA_Type-PROPOSAL_Id-45845_Test_RR1.txt
  </code></pre>
  
  <p>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!</p>