Transform a varchar field with #-delimiters into multiple rows

I am working with a database where some data (integer values representing selected options of a dropdown menue) is saved as a string (varchar) inside only one table column. In

(let's say), there is a

column mapped to a

-options

column. In the latter, data is saved as

#3#9#15#

where 3, 9 and 15 are IDs of dropdown menue options that have been selected by a user. There is another table (let's say

) where each of these options with IDs 1 to 15 are given more attributes (like a

and a

). For statistical reasons, I want to create a MySQL view with the columns

option

and

of products

(the number of products where this option has been selected). To get there, I need the single values from

#3#9#15#

, that is, explode the string somewhere on the way. Now I read that this form of saving data is not encouraged, which is why there is no built-in explode/split function for strings in MySQL. But I cannot change the data format, which has been determined inside a huge software. I didn't make this design choice, but I have to somehow work with it. Working with the built-in function

()

is no choice because the number of selected options saved in this field varies, from 0 to 15. Also, the maximum number is not fix, as new options may be created from time to time. I tried writing an own procedure that will take one number from

#3#9#15#

(for example, by repeatedly trimming the # delimiters) and create a new row with it. The procedure should access the value with a

statement, create a new view with

OR REPLACE VIEW

, and then insert a new row with only the first option (3) option with

INTO viewname(product_id,option) VALUES(@productID, SUBSTR(@options,2,1));

where

@options == #3#9#15#

for example. I tried writing the procedure for only the first option at the beginning, but it already failed at this stage. Other ideas I had included trying to convert the string

@options

into a SET datatype, so I can use

()

function. I also read this question and this article but found them very hard to understand. Having only basic knowledge of MySQL, is there some way for me to create this view? I would be very thankful for any hint in which direction I should go on researching. ***Edit***: I have server version 10.3.28 - MariaDB Server. Do you need more information on this? ***Edit2***: Due to an (ongoing) bug in phpMyAdmin, I could not use SQL

statement (see here ). ***Edit3***: It turned out that the delimiter used by the software was not actually a

#

sign, but was only displayed by phpMyAdmin as such. It was instead the ASCII control character 0001 or "^A".