I have one row that I would like to split into columns for example column name is Code with the row showing 01,04,07 for product items.
I want to split up the product items into columns. Any suggestions would be helpful as I have tried the older functions but realized the SQL version I am working on does not have those options such as string_split and character index.
I used instr() and substring() but a buyer may purchase more or less items than shown above.
Your immediate problem is your design. You should not store multiple integer values in a string column. Each tuple of values should be stored on a separate row.
As for your question: in recent MySQL version, an option is JSON. It might be handier to put the values in rows rather than in columns. Here is one way to do it:
select t.*, x.*
from mytable t
cross join lateral json_table(
concat('[', t.code, ']'),
'$[*]' columns (rn for ordinality, code_part int path '$')
) x
If you do want the results as columns, then one option is conditional aggregation; you need to enumerate as many columns as necessary:
select t.*,
max(case when x.rn = 1 then x.code_part end) as code_part_1,
max(case when x.rn = 2 then x.code_part end) as code_part_2,
...
from mytable t
cross join lateral json_table(
concat('[', t.code, ']'),
'$[*]' columns (rn for ordinality, code_part int path '$')
) x
group by t.id -- assuming that "id" is the primary key of "mytable"
