MySQL 5.7

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


version()


5.7.35


DROP TABLE IF EXISTS parts_listing;
CREATE TABLE parts_listing (
    ID           int
  , PageNo       int
  , BaseGroup    int
  , PartNo       int
);

DROP TABLE IF EXISTS parts_reftype;
CREATE TABLE parts_reftype (
    ID           int
  , chassisNo    int
  , groupNo      int
  , PartNo       int
  , BodyChassis  int
);

DROP TABLE IF EXISTS `parts_parsed`;
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` smallint unsigned DEFAULT NULL,
`bodyNo` smallint unsigned DEFAULT NULL,
`isRHD` tinyint unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1
;


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,
  Models      VARCHAR(50),
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1
;
-- MariaDB 10.5

INSERT INTO parts_parsed (pageNo, baseGroup, partID, partNo, modelNo) 
WITH pairs (p1, p2) AS (
         SELECT * FROM (
                         VALUES ('22ND', '^22')
                              , ('23RD', '^23')
                              , ('24TH', '^24')
                              , ('25TH', '^25')
                              , ('26TH', '^26')
                              , ('54TH', '^54')
                       ) AS x
     )
SELECT PageNo AS pageNo, BaseGroup AS baseGroup
     , pl.ID AS partID, PartNo AS partNo
     , chassisNo AS modelNo 
  FROM parts_listing pl
     , parts_modelno pm
     , pairs
 WHERE Models REGEXP p1
   AND chassisNo REGEXP p2
   AND BaseGroup NOT IN (SELECT GroupNo FROM parts_reftype WHERE BodyChassis = 1)
;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pairs (p1, p2) AS (
         SELECT * FROM (
                         VALUES ('2' at line 4

-- MariaDB 10.5, MySQL 8.0 

INSERT INTO parts_parsed (pageNo, baseGroup, partID, partNo, modelNo) 
WITH pairs (p1, p2) AS (
         SELECT '22ND' AS p1, '^22' AS p2   UNION
         SELECT '23RD', '^23'               UNION
         SELECT '24TH', '^24'               UNION
         SELECT '25TH', '^25'               UNION
         SELECT '26TH', '^26'               UNION
         SELECT '54TH', '^54'               -- etc
     )
SELECT PageNo AS pageNo, BaseGroup AS baseGroup
     , pl.ID AS partID, PartNo AS partNo
     , chassisNo AS modelNo 
  FROM parts_listing pl
     , parts_modelno pm
     , pairs
 WHERE Models REGEXP p1
   AND chassisNo REGEXP p2
   AND BaseGroup NOT IN (SELECT GroupNo FROM parts_reftype WHERE BodyChassis = 1)
;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pairs (p1, p2) AS (
         SELECT '22ND' AS p1, '^22' AS p2   UNION
         S' at line 4

-- MariaDB 10.5, MySQL 8.0, MySQL 5.7

INSERT INTO parts_parsed (pageNo, baseGroup, partID, partNo, modelNo) 
SELECT PageNo AS pageNo, BaseGroup AS baseGroup
     , pl.ID AS partID, PartNo AS partNo
     , chassisNo AS modelNo 
  FROM parts_listing pl
     , parts_modelno pm
     , (
         SELECT '22ND' AS p1, '^22' AS p2   UNION
         SELECT '23RD', '^23'               UNION
         SELECT '24TH', '^24'               UNION
         SELECT '25TH', '^25'               UNION
         SELECT '26TH', '^26'               UNION
         SELECT '54TH', '^54'               -- etc
       ) AS pairs
 WHERE Models REGEXP p1
   AND chassisNo REGEXP p2
   AND BaseGroup NOT IN (SELECT GroupNo FROM parts_reftype WHERE BodyChassis = 1)
;
SELECT '1245' REGEXP '24';
SELECT '1245' REGEXP '^24';
SELECT '2415' REGEXP '^24';


'1245' REGEXP '24'


1




'1245' REGEXP '^24'


0




'2415' REGEXP '^24'


1