Transform a varchar field with #-delimiters into multiple rows

All we need is an easy explanation of the problem, so here it is.

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 products_table (let’s say), there is a product_id column mapped to a selected-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 option_table) where each of these options with IDs 1 to 15 are given more attributes (like a label and a weight).

For statistical reasons, I want to create a MySQL view with the columns selected option and number 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 substring_index() 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 SELECT statement, create a new view with CREATE OR REPLACE VIEW, and then insert a new row with only the first option (3) option with INSERT 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 find_in_set() 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 WITH 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".

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Here is one way to do it:

SELECT zz.`idx`
  FROM (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('#3#9#15#', '#', num.`id`), '#', -1) AS SIGNED) as `idx`
          FROM (SELECT (h*10+t+1) as `id`
                  FROM (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                       (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b) num
                 WHERE num.`id` > 0) zz
 WHERE zz.`idx` > 0
 ORDER BY zz.`idx`;

You can pass up to 100 #-separated IDs into the string and get what you need split out as an UNSIGNED integer.

Method 2

Strongly based on @matigo’s answer, I figured out the following SQL call to be included in phpMyAdmin’s CREATE VIEW viewname AS .... It takes a value from one table column (as well as an ID that is given to this value in another table), splits the value at every instance of a specified delimiter (here: CHAR(1)) and outputs these split values together with the beforementioned ID.

Information about the setting: We have two tables where we take information from, one called split_table_name here (it contains the column split_column_name with values that should be split at the delimiter) and another table called id_table_name (contains a unique ID for the products; this ID is included in split_table_name as a foreign key column called id_table_id).

Please note that the delimiter used by the software was not actually a # sign (as I assumed in my question), but was only displayed by phpMyAdmin as such. It was in fact the ASCII control character 0001. So I needed to convert it to a char with CHAR(1) so I would be able to use it in SUBSTRING_INDEX function.

  SELECT CAST(
      COALESCE(
NULLIF(
SUBSTRING_INDEX ((SUBSTRING_INDEX (split_column_alias, CHAR(1), num.id)), CHAR(1), -1), ''),
          '0')  AS SIGNED)
     as one_split, content_alias.id_column_alias as `id_column_alias` FROM
(SELECT `split_table_alias`.`split_column_name` AS `split_column_alias`, `id_table_alias`.`id` AS `id_column_alias`
FROM `database_name`.`split_table_name` `split_table_alias`
JOIN `database_name`.`id_table_name` `id_table_alias`
ON `id_table_alias`.`id` = `split_table_alias`.`id_table_id`
        ) content_alias
INNER JOIN
(SELECT (h*10+t+1) as id FROM (
      SELECT 0 h  UNION
      SELECT 1    UNION
      SELECT 2    UNION
      SELECT 3    UNION
      SELECT 4    UNION
      SELECT 5    UNION
      SELECT 6    UNION
      SELECT 7    UNION
      SELECT 8    UNION
      SELECT 9) a,
    (
      SELECT 0 t  UNION
      SELECT 1    UNION
      SELECT 2    UNION
      SELECT 3    UNION
      SELECT 4    UNION
      SELECT 5    UNION
      SELECT 6    UNION
      SELECT 7    UNION
      SELECT 8    UNION
      SELECT 9) b ) num
WHERE num.id > 0
) zz
WHERE zz.one_split > 0
ORDER BY zz.one_split;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply