AKAWN

In these examples I’ll use \ as the character.

/*test table*/
DECLARE @t1 TABLE (c1 nvarchar(MAX));
INSERT INTO @t1(c1) VALUES ('D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');

/*data after first \ */
SELECT RIGHT(c1, LEN(c1) - 
       CHARINDEX('\',(c1))
	   ) AS after_first
FROM @t1;

/*data after second \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1, 1)+1)
	 )
	 ) AS after_second
FROM @t1;

/*data after third \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1))
	 )
	 ) AS after_third
FROM @t1;

/*data after fourth \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)))
	 )
	 ) AS after_fourth
FROM @t1;

/*data after last \ */
SELECT RIGHT(c1, CHARINDEX('\',REVERSE(c1))-1) AS after_last
FROM @t1;

/*data before last \ */
SELECT LEFT(c1, LEN(c1) - CHARINDEX('\',REVERSE(c1))) AS before_last
FROM @t1;

/*data before second last \ */
SELECT LEFT(c1,LEN(c1) - CHARINDEX('\', REVERSE(c1), CHARINDEX('\', REVERSE(c1), 0)+1)) AS before_second_last
FROM @t1;

/* data before fourth \ */
SELECT LEFT(c1,  
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)))
	 ) -1
	 ) AS before_fourth
FROM @t1;

/*data before third \ */
SELECT LEFT(c1, 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1))
	 ) -1
	 ) AS before_third
FROM @t1;

/*data before second \ */
SELECT LEFT(c1,
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1, 1)+1)
	 ) -1
	 ) AS before_second
FROM @t1;

/*data before first \ */
SELECT LEFT(c1, 
       CHARINDEX('\',(c1)) -1
	   ) AS before_first 
FROM @t1;

/*between first and second*/
SELECT SUBSTRING(c1
,CHARINDEX('\',(c1))+1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) -1) -
CHARINDEX('\',(c1))
) AS between_first_and_second
FROM @t1;

/*between second and third*/
SELECT SUBSTRING(c1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) +1)
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)) - 
(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) +1))
) AS between_second_and_third
FROM @t1;

/*between third and fourth*/
SELECT SUBSTRING(c1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)) +1)
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1)))))-2) -
(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1)))))
) AS between_third_and_fourth
FROM @t1;