MySQL 8.0

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


version()


8.0.26


CREATE TABLE IF NOT EXISTS `parts_parsed` (
`ID` int unsigned NOT NULL AUTO_INCREMENT,
`pageNo` int unsigned NOT NULL,
`baseGroup` int unsigned NOT NULL,
`partID` int unsigned NOT NULL,
`partNo` varchar(20) NOT NULL,
`modelNo` int unsigned DEFAULT NULL,
`bodyNo` smallint unsigned DEFAULT NULL,
`isRHD` tinyint unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_GENERAL_CI;

DROP TABLE IF EXISTS `parts_modelno`;
CREATE TABLE IF NOT EXISTS `parts_modelno` (
  `ID` smallint unsigned NOT NULL AUTO_INCREMENT,
  `seriesYear` varchar(8) DEFAULT NULL,
  `bodyNo` varchar(8) DEFAULT NULL,
  `chassisNo` varchar(8) DEFAULT NULL,
  `engineNo` varchar(8) DEFAULT NULL,
  `modelDesc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO parts_modelno
(ID,seriesYear,bodyNo,chassisNo) VALUES
('1','1948-49','2213','2213'),('2','1948-49','2250','2226'),
('3','1948-49','2251','2226'),('4','1948-49','2252','2206'),
('5','1948-49','2255','2206'),('26','1949-50','2365','2301'),
('27','1949-50','2372','2302'),('28','1949-50','2375','2302'),
('29','1949-50','2379','2332'),('30','1949-50','2382','2302');

DROP TABLE IF EXISTS `parts_listing`;
CREATE TABLE IF NOT EXISTS `parts_listing` (
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `BaseGroup` INT UNSIGNED DEFAULT NULL,
  `GroupNumber` VARCHAR(20) DEFAULT NULL,
  `BaseName` VARCHAR(50) DEFAULT NULL,
  `GroupName` VARCHAR(50) DEFAULT NULL,
  `Name` VARCHAR(100) DEFAULT NULL,
  `PartNo` VARCHAR(30) DEFAULT NULL,
  `Models` VARCHAR(255) DEFAULT NULL,
  `Description` VARCHAR(255) DEFAULT NULL,
  `Quantity` SMALLINT UNSIGNED DEFAULT NULL,
  `PageNo` INT DEFAULT NULL,
  `SubPage` VARCHAR(5) DEFAULT NULL,
  `RevDate` INT DEFAULT NULL,
  `Edition` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1;

INSERT INTO parts_listing(ID,BaseGroup,GroupNumber,BaseName,NAME,PartNo,Models,Description,Quantity,PageNo,SubPage,RevDate,Edition)

VALUES
('13570','30','30.46796',' BODY',' DOORS-DOOR REAR TRIM PANEL ASSY "SPECIFY TRIM SET NO"','453674','5450-51','RIGHT, SET 50-52','1','402','','-490665600','48-54'),
('1850','3','3.236','CLUTCH AND TRANSMISSION TRANSMISSION','FLANGE-DRIVING SHAFT UNIVERSAL JOINT','302868','2213-20-22-26; 2313','','1','58','','-490665600','48-54'),
('16314','30','30.874934','BODY','RADIATOR GRILLE AND SPLASHER BRACKET-RAD SIDE SPLASHER BRACE FRAME','G120370','2469; 2579; ALL 26TH; 54TH','NUT 7/16-20 2','1','469','','-490665600','48-54'),
('14633','30','30.666983','BODY','ELECTRICAL-ROOF LIGHT GROMMET-ROOF LIGHT CABLE','403834','2280-86','','1','423','','-504921600','48-54'),
('12273','30','30.34395',' BODY','DOORS-DOOR FRONT MOULDING-TRIM PANEL','442793','2677-79-97; 5467-97','INTERMEDIATE, LOWER, RIGHT','1','369','','-490665600','48-54')

WITH RECURSIVE seq AS(
SELECT 1 sn UNION ALL
SELECT sn+1 FROM seq WHERE sn+1 <= 100)
SELECT PageNo AS pageNo, BaseGroup AS baseGroup, pl.ID AS partID, PartNo AS partNo, chassisNo AS modelNo 
FROM
(SELECT *,
       REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(Models,';',sn),';',-1),
             '[^0-9]','') sval
 FROM seq JOIN parts_listing pl
 ON sn <= (LENGTH(Models)-LENGTH(REPLACE(Models,';','')))+1) pl
 JOIN
 parts_modelno pm 
 ON (LEFT(pl.sval,2)=LEFT(pm.bodyNo,2) OR
 LEFT(pl.sval,2)=LEFT(pm.chassisNo,2))
 GROUP BY PageNo, BaseGroup, partID, PartNo, modelNo
 ORDER BY PageNo, BaseGroup, partID, PartNo, modelNo ;
 


pageNo
baseGroup
partID
partNo
modelNo


58
3
1850
302868
2206

58
3
1850
302868
2213

58
3
1850
302868
2226

58
3
1850
302868
2301

58
3
1850
302868
2302

58
3
1850
302868
2332

423
30
14633
403834
2206

423
30
14633
403834
2213

423
30
14633
403834
2226


INSERT INTO parts_parsed (pageNo, baseGroup, partID, partNo, modelNo) 
WITH RECURSIVE seq AS(
SELECT 1 sn UNION ALL
SELECT sn+1 FROM seq WHERE sn+1 <= 100)
SELECT PageNo AS pageNo, BaseGroup , pl.ID AS partID, PartNo, chassisNo AS modelNo 
FROM
(SELECT *,
       TRIM(REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(Models,';',sn),';',-1),
             '[^0-9]','')) sval
 FROM seq JOIN parts_listing pl
 ON sn <= (LENGTH(Models)-LENGTH(REPLACE(Models,';','')))+1) pl
 JOIN
 parts_modelno pm 
 ON (LEFT(pl.sval,2)=LEFT(pm.bodyNo,2) OR
 LEFT(pl.sval,2)=LEFT(pm.chassisNo,2))
 GROUP BY PageNo, BaseGroup, partID, PartNo, modelNo
 ORDER BY PageNo, BaseGroup, partID, PartNo, modelNo ;
 
SELECT * FROM parts_parsed;


ID
pageNo
baseGroup
partID
partNo
modelNo
bodyNo
isRHD


1
58
3
1850
302868
2206
null
0

2
58
3
1850
302868
2213
null
0

3
58
3
1850
302868
2226
null
0

4
58
3
1850
302868
2301
null
0

5
58
3
1850
302868
2302
null
0

6
58
3
1850
302868
2332
null
0

7
423
30
14633
403834
2206
null
0

8
423
30
14633
403834
2213
null
0

9
423
30
14633
403834
2226
null
0
pageNo	baseGroup	partID	partNo	modelNo
58	3	1850	302868	2206
58	3	1850	302868	2213
58	3	1850	302868	2226
58	3	1850	302868	2301
58	3	1850	302868	2302
58	3	1850	302868	2332
423	30	14633	403834	2206
423	30	14633	403834	2213
423	30	14633	403834	2226