postgresql - split column to row based on string length

Question

I'm new to the database, so please pardon me if this is basis. I have a list of product codes merged in a column/cell of a table for each product type like below

codes                | type
----------------------------------
SM01SM02SG41GD2ZDM5  | Medicine 
SN01ML2 ML2H         | Food  

I need such data to be converted as below (per every 4 character of code a new row has be constructed) through sql in Posrgresql database.

code | type      | item-number
----------------------------------
SM01 | Medicine  | 1
SM02 | Medicine  | 2
SG41 | Medicine  | 3
GD2Z | Medicine  | 4
DM5  | Medicine  | 5
SN01 | Food      | 2
ML2  | Food      | 3
ML2H | Food      | 1

Any advise/assistance is much appreciated.

Answer 1

A recursive cte can do this:

WITH recursive cte as (
   SELECT 'SM01SM02SG41GD2ZDM5' as codes, 'Medicine' as type
   union all
   SELECT 'SN01ML2 ML2H', 'Food' 
),
cte1 as (
   SELECT SUBSTRING(codes,1,4) as code, cte.type as "type", 1 as item_number
   from cte
   
   union all
   
   select  substring(codes,item_number*4+1,4), cte1.type, item_number+1 
   from cte1
   inner join cte on cte.type=cte1.type
   where item_number < length(codes)/4+1
      )
select * 
from cte1
where code<>''
order by type desc, item_number
;   

DBFIDDLE

output:

code	type	item_number
SM01	Medicine	1
SM02	Medicine	2
SG41	Medicine	3
GD2Z	Medicine	4
DM5	Medicine	5
SN01	Food	1
ML2	Food	2
ML2H	Food	3