Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as
    select 1 as category_id, '1;2;3;4' as product_id union all
    select 2 as category_id, '7;8;9' as product_id union all
    select 3 as category_id, '6' as product_id

SELECT 3


select t.category_id, r.product_id
from t cross join lateral
     regexp_split_to_table(t.product_id, ';') r(product_id)


category_id
product_id


1
1

1
2

1
3

1
4

2
7

2
8

2
9

3
6



SELECT 8


select t.category_id, regexp_split_to_table(t.product_id, ';') as product_id
from t 


category_id
product_id


1
1

1
2

1
3

1
4

2
7

2
8

2
9

3
6



SELECT 8