The tally table and/or spring split functions are good ways to go, but recursive CTEs are interesting to learn and I have used them in many one-offs where I can't program in the database. Here is one I have had bookmarked from stackoverflow to help me learn that I modified for here.

/*
    Use a recursive CTE and the CHARINDEX function to break a URL into substrings at each "/".
    A modified script found on stackoverflow: https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string
*/

GO
if OBJECT_ID('tempdb..#t_Strings') is not null
BEGIN
Drop Table #t_Strings;
END;
GO
if OBJECT_ID('tempdb..#t_Interim') is not null
BEGIN
Drop Table #t_Interim;
END;

create table #t_Strings(
    URL_String varchar(255) NULL
    );

insert into #t_Strings (URL_String)
select 'http://stable-test.test.net/api/files/fs.testcollection/TP-0000000.pdf/download/' union all
select 'http://stable-test.test.net/api_state/file/fs.anothercollection/TP-1111111.exe/upload/' union all
select 'https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string';

with cte_Strings(URL_String, Nth_Start, Nth_Found) as (
  select URL_String, 1, charindex('/', URL_String) from #t_Strings
  union all
  select URL_String, Nth_Found + 1, charindex('/', URL_String, Nth_Found + 1)
  from cte_Strings
  where Nth_Found > 0
)
select URL_String, N = ROW_NUMBER() over (partition by URL_String order by Nth_Start), Nth_Start, Nth_Found,
    substring(URL_String, Nth_Start, case when Nth_Found > 0 then Nth_Found - Nth_Start else len(URL_String) end) as Nth_Substring
into #t_Interim
from cte_Strings
order by URL_String, Nth_Start;

-- All substrings numbered
select *
from #t_Interim
order by URL_String, N;

-- Desired Result
select *
from #t_Interim
where N=6
order by URL_String;