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

  • sathwik.em91

    SSC Eights!

    Points: 806

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

    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'

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

    Thanks in advance.

    • This topic was modified 4 weeks, 1 day ago by  sathwik.em91.
  • Jeff Moden

    SSC Guru

    Points: 996843

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • sathwik.em91

    SSC Eights!

    Points: 806

    @jeff Sorry for the typo.Corrected it.

  • Phil Parkin

    SSC Guru

    Points: 244662

    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().

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720484

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

  • pietlinden

    SSC Guru

    Points: 62848

    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 weeks, 1 day ago by  pietlinden.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply