By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @TABLE_A AS TABLE
(
[Start] varchar(20),
[End] varchar(20),
[Qty] numeric(10)
)
DECLARE @TABLE_B AS TABLE
(
[Start] varchar(20),
[End] varchar(20),
[Qty] numeric(10)
)
INSERT INTO @TABLE_A VALUES('ABC001','ABC005',5)
INSERT INTO @TABLE_A VALUES('ABC007','ABC007',1)
INSERT INTO @TABLE_A VALUES('AC0002','AC0003',2)
;WITH CTE AS (
SELECT SUBSTRING([Start],0,PATINDEX('%[0-9]%', [Start])) as prefix,
CAST(SUBSTRING([Start],PATINDEX('%[0-9]%', [Start]),LEN([Start]) - PATINDEX('%[0-9]%', [Start]) + 1) AS INT) fromNum,
CAST(SUBSTRING([End],PATINDEX('%[0-9]%', [End]),LEN([End]) - PATINDEX('%[0-9]%', [End]) + 1) AS INT) toNum,
PATINDEX('%[1-9]%', [Start]) - PATINDEX('%[0-9]%', [Start]) paddingOfZero
FROM @TABLE_A
UNION ALL
SELECT prefix,
fromNum +1,
toNum,
paddingOfZero
FROM CTE
WHERE fromNum +1<=toNum
)
INSERT INTO @TABLE_B ([Start],[End],Qty)
select CONCAT(prefix,FORMAT(fromNum,REPLICATE('0',paddingOfZero + 1))),
CONCAT(prefix,FORMAT(fromNum,REPLICATE('0',paddingOfZero + 1))),
| Start | End | Qty |
|---|---|---|
| ABC001 | ABC001 | 1 |
| ABC007 | ABC007 | 1 |
| AC0002 | AC0002 | 1 |
| AC0003 | AC0003 | 1 |
| ABC002 | ABC002 | 1 |
| ABC003 | ABC003 | 1 |
| ABC004 | ABC004 | 1 |
| ABC005 | ABC005 | 1 |