Find the last occurrence of \ character and get the left of string.

• sathwik.em91

  SSC Eights!

  Points: 878

  More actions

  July 13, 2020 at 4:32 pm

  #3769455

  How to find the last occurrence of '\' in the following  string  and get left  part of it?

  DECLARE @v-2  VARCHAR(max)='O:\Z\P_Metrics\Inbox\07-13-20200702_abc_enroll.csv'

  DECLARE @V1  VARCHAR(max)='O:\Z\P_Metrics\Inbox\07-14-20200702_abc_complete_enroll.csv'

  The  Final output I need is O:\Z\P_Metrics\Inbox.

  Thanks in advance.

  • This topic was modified 4 years, 10 months ago by sathwik.em91.

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  July 13, 2020 at 4:52 pm

  #3769459

  What version of T-SQL are you actually using?  I ask because there's no such thing as "VARCHR" in T-SQL.  There is "VARCHAR" but it has two "A"s in it instead of just 1.

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sathwik.em91

  SSC Eights!

  Points: 878

  More actions

  July 13, 2020 at 4:59 pm

  #3769461

  @jeff Sorry for the typo.Corrected it.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  July 13, 2020 at 5:03 pm

  #3769464

  If your file names always follow that consistent pattern, you could use something like this:

  DECLARE @v VARCHAR(MAX) = 'O:\Z\P_Metrics\Inbox\07-13-20200702_abc_enroll.csv';
  DECLARE @V1 VARCHAR(MAX) = 'O:\Z\P_Metrics\Inbox\07-14-20200702_abc_complete_enroll.csv';
  
  SELECT LEFT(@v, PATINDEX('%[0-9][0-9]-[0-9][0-9]-%', @v) - 2);
  
  SELECT LEFT(@V1, PATINDEX('%[0-9][0-9]-[0-9][0-9]-%', @V1) - 2);

  Your (more expensive) alternative is to use a combination of REVERSE(), CHARINDEX(), LEN() and LEFT().

  ---

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738043

  More actions

  July 13, 2020 at 5:26 pm

  #3769470

  Phil has good advice. If the patterns aren't consistent, then use REVERSE and find the first occurrence.

• pietlinden

  SSC Guru

  Points: 63573

  More actions

  July 13, 2020 at 6:42 pm

  #3769502

  DECLARE @v VARCHAR(100)='O:\Z\P_Metrics\Inbox\07-13-20200702_abc_enroll.csv';
  
  DECLARE @V1 VARCHAR(100)='O:\Z\P_Metrics\Inbox\07-14-20200702_abc_complete_enroll.csv';

  Solution:

  SELECT LEFT(@V1,LEN(@V1) - CHARINDEX('\',REVERSE(@V1),1))

  Returns:

  O:\Z\P_Metrics\Inbox

  O:\Z\P_Metrics\Inbox

  • This reply was modified 4 years, 10 months ago by pietlinden.