By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @SOURCE_FILE VARCHAR(255) = '\\long\path\file_name_something_String1_String2_String3_datetime.csv'
declare @sub1 varchar(100)
declare @sub2 varchar(100)
declare @sub3 varchar(100)
select @sub1 = String1, @sub2 = String2, @sub3 = String3
from ( select t2.value,
t2.value as ColumnName
from ( select t.value,
t.one,
row_number() over (partition by t.one order by t.one) AS number,
(select count(value) from string_split(@SOURCE_FILE, '_')) as total
from ( select 1 as one,
value
from string_split(@SOURCE_FILE, '_')
where value is not null
and value <> ''
) t
) t2
where t2.number > 3
and t2.number < t2.total
) t3
pivot
(
max(value)
for ColumnName in (String1, String2, String3)
) p
select @sub1 as sub1, @sub2 as sub2, @sub3 as sub3
| sub1 | sub2 | sub3 |
|---|---|---|
| String1 | String2 | String3 |