locked
CHARINDEX with REVERSE RRS feed

  • Question

  • User-1826049516 posted

    Hey,

    I need to get the position of the end of a string.

    declare @FilePath nvarchar(255) = 'Y:\Parent_Folder_Path\A\A1\A12345AB';
    print charindex('Parent_Folder_Path', @FilePath);

    Returns 4. I need it to return 21.

    Basically I need to change 'Y:\Parent_Folder_Path' with a new folder. But it's not that easy because Y:\Parent_Folder_Path could be any of these:

    Y:\Parent_Folder_Path
    \\dfsroot\dfsfolder\Parent_Folder_Path
    \\realserver1\share\Parent_Folder_Path
    \\realserver2\share\Parent_Folder_Path

    So basically I want to replace from the start of the @FilePath string upto the end of the common folder (Parent_Folder_Path) with something else.

    set @FilePath = replace(@FilePath, substring(@FilePath, 0, charindex('Parent_Folder_Path', @FilePath)), '\\dfsroot\dfsfolder\newpath');

    So the result would be: \\dfsroot\dfsfolder\newpath\A\A1\A12345AB

    Thanks

    Friday, October 6, 2017 3:57 PM

Answers

  • User2103319870 posted

    ldoodle

    So the result would be: \\dfsroot\dfsfolder\newpath\A\A1\A12345AB

    You can try with the below code

    DECLARE @FilePath nvarchar(255) = 'Y:\Parent_Folder_Path\A\A1\A12345AB';
    
    Select '\\dfsroot\dfsfolder\newpath' + REVERSE(SUBSTRING(REVERSE(@FilePath), 1, CHARINDEX(REVERSE('Parent_Folder_Path'), REVERSE(@FilePath), 1) - 1));  -- Results in \\dfsroot\dfsfolder\newpath\A\A1\A12345AB
    
    
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 6, 2017 4:24 PM