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;