0

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".

3
  • data is saved as #3#9#15# where 3, 9 and 15 are IDs Storing multiple data in one column in CSV from (your format is modified CSV) is bad practice. Normalizing strongly recommended.
    – Akina
    Commented May 11, 2021 at 15:09
  • is there some way for me to create this view? Of course. Specify precise version of your server.
    – Akina
    Commented May 11, 2021 at 15:09
  • Does this help? stackoverflow.com/questions/17942508/…
    – user212533
    Commented May 11, 2021 at 15:37

2 Answers 2

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.

0
0

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;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.