Post reply

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

  • 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, 4 months ago by  sathwik.em91.

  • 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)

  • July 13, 2020 at 4:59 pm

    #3769461

    @jeff Sorry for the typo.Corrected it.

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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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.

  • 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, 4 months ago by  pietlinden.

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

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