Equivalent of explode() to work with strings in MySQL

Building a custom SPLIT_STRING function

Having a custom function in MySQL is liberating, providing flexibility when you work with strings. To illustrate, let's mimic PHP's explode() function in MySQL by developing a SPLIT_STRING function:

# Define | Rule the world
DELIMITER $$
CREATE FUNCTION SPLIT_STRING(str TEXT, delim VARCHAR(12), pos INT) RETURNS TEXT
BEGIN
  # It's time to explode...in a UNICODE-safe way!
  RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(str, delim, pos), delim, -1);
END$$
DELIMITER ;


This function can be precisely used to compare data elements like sports scores, where the order can vary:

# Game on!
SELECT SPLIT_STRING(score, '-', 1) AS team1_score,
       SPLIT_STRING(score, '-', 2) AS team2_score
FROM game_results;


Creating ordered split strings via stored procedures

Those who say MySQL doesn't support AUTO_INCREMENT feature in temporary tables have not tried stored procedures. With REPLACE, CONCAT, and temporary tables, you can split strings with an inherent order:

# Challenge accepted!
DELIMITER $$
CREATE PROCEDURE ExplodeString(source VARCHAR(255), delimiter VARCHAR(5))
BEGIN
  DECLARE id INT DEFAULT 0;
  DECLARE value VARCHAR(255);

  # Cleanup! Expensive operation though – use with caution.
  DROP TABLE IF EXISTS temp_explode;
  CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY, value VARCHAR(255));

  WHILE CHAR_LENGTH(source) > 0 DO
    SET value = SUBSTRING_INDEX(source, delimiter, 1);
    INSERT INTO temp_explode (value) VALUES (value);
    SET source = REPLACE(source, CONCAT(value, delimiter), '');
    SET id = id + 1;
  END WHILE;

  SELECT value FROM temp_explode ORDER BY id;
END$$
DELIMITER ;


To use the stored procedure:

# Pick up the phone...proceduureee!
CALL ExplodeString('score1-score2-score3', '-');


Reversing roles with SUBSTRING_INDEX

By using the SUBSTRING_INDEX, you can effortlessly access elements even if your data is in reverse order:

# Explode it! ... Now walk it back...
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(field, delimiter, position), delimiter, -1) as part
FROM your_table;


Using string functions for data comparisons

Comparing numerical strings

While working with strings, you often need to perform numerical comparisons. In this context, CAST() or CONVERT() functions come in handy:

# Goal! ... Oh wait, it's offside
SELECT CAST(SPLIT_STRING(score, '-', 1) AS UNSIGNED) AS team1_score,
       CAST(SPLIT_STRING(score, '-', 2) AS UNSIGNED) AS team2_score
FROM game_results;


Finding substrings with CHAR_LENGTH and LOCATE

If you need to find positions of specific substrings, CHAR_LENGTH() and LOCATE() are the experts:

# Where is Waldo (the delimiter)? 
SELECT LOCATE('-', field) FROM your_table;
