SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tabOld
(
  name nvarchar(200)
);

insert into tabOld (name) values
('5522-rep__-4210-03-test-434907-emn-nt.pdf');

select * from tabOld;


name


5522-rep__-4210-03-test-434907-emn-nt.pdf


create table tabNew
(
  name nvarchar(200),
  num int
);

select * from tabNew;


name
num


-- split on '-' and add number
with cte as
(
  select convert(xml, '<r>'+replace(old.name, '-', '</r><r>')+'</r>') as XMLCol
  from tabOld old
)
select row_number() over(order by (select null)) as Num,
       n.r.value('.', 'nvarchar(200)') as SubStr
from cte
cross apply cte.XMLCol.nodes('r') as n(r);


Num
SubStr


1
5522

2
rep__

3
4210

4
03

5
test

6
434907

7
emn

8
nt.pdf


-- select 3rd value, convert to integer and insert
with cteXml as
(
  select old.name,
         convert(xml, '<r>'+replace(old.name, '-', '</r><r>')+'</r>') as XMLCol
  from tabOld old
),
cteSplit as
(
  select cx.name,
         row_number() over(order by (select null)) as Num,
         n.r.value('.', 'nvarchar(200)') as SubStr
  from cteXml cx
  cross apply cx.XMLCol.nodes('r') as n(r)
)
insert into tabNew (name, num)
select cs.name,
       convert(int, cs.SubStr)
from cteSplit cs
where cs.Num = 3;
1 rows affected

select * from tabNew;


name
num


5522-rep__-4210-03-test-434907-emn-nt.pdf
4210