MySQL 8.0

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


version()


8.0.27


create table products (
  id int auto_increment,
  upc varchar(50),
  upc_variation text,
  primary key (id),
  index (upc),
  fulltext index (upc_variation)
);
insert into products (upc, upc_variation) values
  ('01234', '01234,12345,23456'),
  ('56789', '45678,34567'),
  ('056789', '045678,034567');
select p.*
from products p
where match (upc_variation) against ('12345 34567');


id
upc
upc_variation


1
01234
01234,12345,23456

2
56789
45678,34567


select p.*
from products p
where json_overlaps( -- requires 8.0.17+
  '["12345","34567"]',
  concat('["', replace(upc_variation, ',', '","'), '"]')
);


id
upc
upc_variation


1
01234
01234,12345,23456

2
56789
45678,34567


select distinct p.*
from products p
join json_table( -- requires 8.0.4+
  concat('["', replace(p.upc_variation, ',', '","'), '"]'),
  '$[*]' columns (upcv text path '$')
) v 
where v.upcv in ('12345', '34567');


id
upc
upc_variation


1
01234
01234,12345,23456

2
56789
45678,34567


create table products_upc_variations (
  product_id int,
  upc_variation varchar(50),
  primary key (product_id, upc_variation),
  index  (upc_variation, product_id)
);
insert into products_upc_variations (product_id, upc_variation) values 
  (1, '01234'),
  (1, '12345'),
  (1, '23456'),
  (2, '45678'),
  (2, '34567'),
  (3, '045678'),
  (3, '034567');
select p.*
from products p
join products_upc_variations v on v.product_id = p.id
where v.upc_variation in ('12345', '34567');


id
upc
upc_variation


1
01234
01234,12345,23456

2
56789
45678,34567


select p.*
from products p
where p.upc_variation rlike '(^|,)(12345|34567)(,|$)'


id
upc
upc_variation


1
01234
01234,12345,23456

2
56789
45678,34567