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 |