Splitting Strings Using Built-in Tables

To transform a comma-separated string into rows, the help_topic system table can be used. This approach leverages the sequential IDs present in the table.

SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX('a,b,c,d,e,f,g,h', ',', help_topic_id + 1), 
    ',', 
    -1
  ) AS extracted_value
FROM mysql.help_topic
WHERE help_topic_id < (
  LENGTH('a,b,c,d,e,f,g,h') - LENGTH(REPLACE('a,b,c,d,e,f,g,h', ',', '')) + 1
);

Important considerations when using help_topic:

• The number of elements in the comma-separated list must not exceed the maximum ID in help_topic, otherwise truncation may occur.
• The help_topic table must contain data with continuous incrementing IDs starting from zero. An empty table will yield no results.

Practical Application Scenario

Given an order cancellation record where multiple reasons are stored as a comma-separated string, the objective is to count how many times each individual reason appears.

Transforming columns into rows:

SELECT 
  moe.id,
  moe.order_id,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(moe.reason, ',', ht.help_topic_id + 1), 
    ',', 
    -1
  ) AS split_reason
FROM mall_order_examine moe
JOIN mysql.help_topic ht 
  ON ht.help_topic_id < (
    LENGTH(moe.reason) - LENGTH(REPLACE(moe.reason, ',', '')) + 1
  )
ORDER BY moe.order_id;

Aggregated statistics query:

SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(moe.reason, ',', ht.help_topic_id + 1), 
    ',', 
    -1
  ) AS cancellation_reason,
  COUNT(moe.order_id) AS occurrence_count
FROM mall_order_examine moe
JOIN mysql.help_topic ht 
  ON ht.help_topic_id < (
    LENGTH(moe.reason) - LENGTH(REPLACE(moe.reason, ',', '')) + 1
  )
GROUP BY 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(moe.reason, ',', ht.help_topic_id + 1), 
    ',', 
    -1
  );

Alternative Approach Without System Table Dependency

In cases where help_topic is unavailable or lacks sufficient entries, a custom auxiliary table with sequential integer IDs can be created. This ensures that the ID range exceeds the expected segment count for all records.

Column-to-row transformation using a custom sequence table:

SELECT 
  moe.order_id,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(moe.reason, ',', seq.id), 
    ',', 
    -1
  ) AS segmented_reason
FROM mall_order_examine moe
JOIN sequence_table seq 
  ON seq.id <= (
    LENGTH(moe.reason) - LENGTH(REPLACE(moe.reason, ',', '')) + 1
  )
ORDER BY moe.order_id;

Statistical summary using custom table:

SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(moe.reason, ',', seq.id), 
    ',', 
    -1
  ) AS reason_label,
  COUNT(moe.order_id) AS frequency
FROM mall_order_examine moe
JOIN sequence_table seq 
  ON seq.id <= (
    LENGTH(moe.reason) - LENGTH(REPLACE(moe.reason, ',', '')) + 1
  )
GROUP BY 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(moe.reason, ',', seq.id), 
    ',', 
    -1
  );

Core Logic Explanation

The underlying mechanism involves joining the source dataset with a table containing sequential integers. This provides index positions required for parsing delimited strings.

The expression (LENGTH(field) - LENGTH(REPLACE(field, ',', '')) + 1) calculates the total number of segments after splitting.

Depending on whether indexing starts at 0 (help_topic) or 1 (sequence_table), comparison operators differ:

• For zero-based sequences: help_topic_id < calculated_length
• For one-based sequences: id <= calculated_length

Data extraction relies on nested SUBSTRING_INDEX calls. When working with zero-based indices, increment by one during the first substring operation. One-based indexing works directly with the current ID value.