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 |