How to get stored data by delimiter ( ; ) in mysql?

@domenico 

You can get stored data by delimiter ( ; ) in MySQL using the following steps:

1. Use the SUBSTRING_INDEX function to extract the data between delimiters. For example, if you have a column named "data" containing values separated by semicolons, you can use the following query to extract the data between the first and second semicolon:

1
2

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(data, ';', 2), ';', -1) AS extracted_data
FROM your_table;

This query will extract the data between the first and second semicolon in the "data" column and display it as "extracted_data".

1. You can also use the REPLACE function to replace the delimiters with a different delimiter, making it easier to extract the data. For example, to replace semicolons with a comma, you can use the following query:

1
2

SELECT REPLACE(data, ';', ',') AS replaced_data
FROM your_table;

This query will replace all semicolons in the "data" column with commas and display it as "replaced_data".

1. If you want to extract all the values separated by semicolons into separate rows, you can use a combination of the SUBSTRING_INDEX and CONCAT functions with a recursive Common Table Expression (CTE). Here is an example query to achieve this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

WITH RECURSIVE cte AS (
  SELECT 1 AS n, 1 AS start_idx, data
  FROM your_table
  UNION ALL
  SELECT n + 1, 
         LOCATE(';', data, start_idx) + 1 AS start_idx,
         data
  FROM cte
  WHERE LOCATE(';', data, start_idx) > 0
)
SELECT SUBSTRING(data, start_idx, 
                 IF(LOCATE(';', data, start_idx) > 0, 
                    LOCATE(';', data, start_idx) - start_idx, 
                    LENGTH(data))) AS extracted_data
FROM cte;

This query will extract all values separated by semicolons in the "data" column into separate rows.