SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) 
 Aug 15 2017 10:23:29 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)



create table data(Materialno_start varchar(20), 
                  Materialno_end varchar(20),
                  Name varchar(10), 
                  MType varchar(10), 
                  Noofstock int);

insert into data values('101-31101','101-31105','Pendrive','Hardware',12);
insert into data values('101-41234','101-41236','Mcafee','Software',5);
insert into data values('202-54367','202-54368','Keyboard','Hardware',2);
insert into data values('405-34455',NULL,'Mouse','Hardware',20);
insert into data values('321-98765','321-98766','Cable','Hardware',6);
5 rows affected

 select * from data


Materialno_start
Materialno_end
Name
MType
Noofstock


101-31101
101-31105
Pendrive
Hardware
12

101-41234
101-41236
Mcafee
Software
5

202-54367
202-54368
Keyboard
Hardware
2

405-34455
null
Mouse
Hardware
20

321-98765
321-98766
Cable
Hardware
6


select * from data 
cross apply (VALUES(len(Materialno_start))) leng(mn)
cross apply (VALUES(charindex('-', Materialno_start) )) s(hyp)
cross apply (VALUES(substring(Materialno_start,s.hyp-leng.mn+1,leng.mn))) n(base)
cross apply (VALUES(substring(Materialno_start,s.hyp+1,leng.mn))) starts(st)
cross apply (VALUES(substring(coalesce(Materialno_end,Materialno_start),s.hyp+1,leng.mn))) ends(ed)
cross apply (VALUES(convert(int,ends.ed)-convert(int,starts.st) )) diff(s)


Materialno_start
Materialno_end
Name
MType
Noofstock
mn
hyp
base
st
ed
s


101-31101
101-31105
Pendrive
Hardware
12
9
4
101-
31101
31105
4

101-41234
101-41236
Mcafee
Software
5
9
4
101-
41234
41236
2

202-54367
202-54368
Keyboard
Hardware
2
9
4
202-
54367
54368
1

405-34455
null
Mouse
Hardware
20
9
4
405-
34455
34455
0

321-98765
321-98766
Cable
Hardware
6
9
4
321-
98765
98766
1


with cte as
(
select Materialno_start,Materialno_end,name,mtype,noofstock,starts.st as ns,ends.ed as nd,diff.s as d,i=1,n=convert(varchar(30),starts.st),n.base as bs from data 
cross apply (VALUES(len(Materialno_start))) leng(mn)
cross apply (VALUES(charindex('-', Materialno_start) )) s(hyp)
cross apply (VALUES(substring(Materialno_start,s.hyp-leng.mn+1,leng.mn))) n(base)
cross apply (VALUES(substring(Materialno_start,s.hyp+1,leng.mn))) starts(st)
cross apply (VALUES(substring(coalesce(Materialno_end,Materialno_start),s.hyp+1,leng.mn))) ends(ed)
cross apply (VALUES(convert(int,ends.ed)-convert(int,starts.st) )) diff(s)
union all
select Materialno_start,Materialno_end,name,mtype,noofstock,ns,nd,d,i=i+1,n=convert(varchar(30),n+1),bs from cte
where i<=d

)
select Materialno_start,Materialno_end,Name,mtype,noofstock,bs+n as newid from cte order by 1


Materialno_start
Materialno_end
Name
mtype
noofstock
newid


101-31101
101-31105
Pendrive
Hardware
12
101-31101

101-31101
101-31105
Pendrive
Hardware
12
101-31102

101-31101
101-31105
Pendrive
Hardware
12
101-31103

101-31101
101-31105
Pendrive
Hardware
12
101-31104

101-31101
101-31105
Pendrive
Hardware
12
101-31105

101-41234
101-41236
Mcafee
Software
5
101-41234

101-41234
101-41236
Mcafee
Software
5
101-41235

101-41234
101-41236
Mcafee
Software
5
101-41236

202-54367
202-54368
Keyboard
Hardware
2
202-54367

202-54367
202-54368
Keyboard
Hardware
2
202-54368

321-98765
321-98766
Cable
Hardware
6
321-98765

321-98765
321-98766
Cable
Hardware
6
321-98766

405-34455
null
Mouse
Hardware
20
405-34455
Materialno_start	Materialno_end	Name	MType	Noofstock
101-31101	101-31105	Pendrive	Hardware	12
101-41234	101-41236	Mcafee	Software	5
202-54367	202-54368	Keyboard	Hardware	2
405-34455	null	Mouse	Hardware	20
321-98765	321-98766	Cable	Hardware	6