MySQL Query to split out Delimited String values to Separate Columns

Question

As part of a migration task I need to split out values in an asterisk-delimited string to separate columns. For example, given this string:

Case Western Reserve University* University of Kansasi*Middle East Tech

I want to return:

Uni1	Uni2	Uni3
Case Western Reserve University	University of Kansasi	Middle East Tech

There can be any number of delimited values in the string, so the query needs to be dynamic.

The approach I am working on is using a stored procedure to loop through the string to effectively recreate this:

SET @test = "Case Western Reserve University* University of Kansasi* Middle East Technical University";


SELECT SPLIT_STR (@test, '*',3) as third, SPLIT_STR (@test, '*',2) as second;

This is what I have so far. It's a work in progress. It loops through the delimited values and uses a function (SPLIT_STR) to pull out the value, but returns the values in one table cell. I've not been able to add 'as val1' to @rval, hence it not splitting into separate columns.

DELIMITER //

CREATE PROCEDURE SplitValuesIntoColumns ( delim_str VARCHAR(1000) )

BEGIN

   DECLARE i INT;
  
   SET i = 0;
  
   WHILE (i <= 2) DO
    SET i = i + 1;
        SET @rval =  concat(@rval,SPLIT_STR (delim_str, '*', 1));  
   END WHILE;
   
   SELECT @rval;


END; //

DELIMITER ;

Any help appreciated. This seems long winded, so if there's a better way to achieve this, please chime in.

score 0 · Answer 1 · answered Mar 16 '21 at 18:10

0

SUBSTRING_INDEX(SUBSTRING_INDEX(column, '*', xxx), '*', -1) extracts substring number xxx:

SET @test = "Case Western Reserve University* University of Kansasi* Middle East Technical University";

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@test, '*', 1), '*', -1) first,
       SUBSTRING_INDEX(SUBSTRING_INDEX(@test, '*', 2), '*', -1) second,
       SUBSTRING_INDEX(SUBSTRING_INDEX(@test, '*', 3), '*', -1) third;

If entered substring number is over the substrings amount then some last substrings returned will be the same.

answered Mar 16 '21 at 18:10

Akina

39,301
5
14
25

Thanks for your answer, but the solution needs to be dynamic. There could be one value in the delimited string or ten.. Hence the loop. – user15409152 Mar 17 '21 at 08:10
@user15409152 What is **precise** MySQL version? If 8+ then use recursive CTE and generate according numbers table. In 5+ generate it statically in subquery. Accordingly use `ON number <= LENGTH(text) - LENGTH(REPLACE(text, '*', '') + 1)` – Akina Mar 17 '21 at 08:14

MySQL Query to split out Delimited String values to Separate Columns

1 Answers1