SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

    CREATE FUNCTION dbo.SubstringIndex
    (
      @input nvarchar(max),
      @delim nchar(1),
      @index int
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS
      RETURN (SELECT str = value
        FROM STRING_SPLIT(@input, @delim, 1)
        WHERE ordinal = @index);
CREATE TABLE dbo.SourceData
  (
    Id int identity,
    FilePath nvarchar(max)
  );

INSERT dbo.SourceData(FilePath) VALUES
  (N'pw:\\abcdef\Documents\_Demonstration\PROJ1_JAC\30 Project Data\AJAC-XX-00-D-A-0001'),
  (N'pw:\\something\much\simpler\than\that');
2 rows affected

SELECT * FROM dbo.SourceData CROSS APPLY dbo.SubstringIndex(FilePath, N'\', 5);


Id
FilePath
str


1
pw:\abcdef\Documents\_Demonstration\PROJ1_JAC\30 Project Data\AJAC-XX-00-D-A-0001
_Demonstration

2
pw:\something\much\simpler\than\that
simpler

Id	FilePath	str
1	pw:\abcdef\Documents\_Demonstration\PROJ1_JAC\30 Project Data\AJAC-XX-00-D-A-0001	_Demonstration
2	pw:\something\much\simpler\than\that	simpler