[mysql] Equivalent of explode() to work with strings in MySQL

In MySQL, I want to be able to search for '31 - 7', when another value = '7 - 31'. What is the syntax that I would use to break apart strings in MySQL? In PHP, I would probably use explode(' - ',$string) and put them together. Is there a way to do this in MySQL?

Background: I'm working with sports scores and want to try games where the scores are the same (and also on the same date) - the listed score for each team is backwards compare to their opponent's database record.

The ideal MySQL call would be:

Where opponent1.date  = opponent2.date
  AND opponent1.score = opponent2.score

(opponent2.score would need to be opponent1.score backwards).

Use this function. It works like a charm. replace "|" with the char to explode/split and the values 1,2,3,etc are based on the number of entries in the data-set: Value_ONE|Value_TWO|Value_THREE.

SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 1), '|', -1) AS PSI,
SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 2), '|', -1) AS GPM,
SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 3), '|', -1) AS LIQUID

I hope this helps.

You can use stored procedure in this way..

DELIMITER |

CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)                                
BEGIN                                
  DROP TABLE IF EXISTS temp_explode;                                
  CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));                                
  SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');                                
  PREPARE myStmt FROM @sql;                                
  EXECUTE myStmt;                                
END |   

DELIMITER ;

• example call:

   SET @str  = "The quick brown fox jumped over the lazy dog"; 
   SET @delim = " "; 
  
  CALL explode(@delim,@str);
  SELECT id,word FROM temp_explode;
  

use substring_index, in the example below i have created a table with column score1 and score2, score1 has 3-7, score2 7-3 etc as shown in the image. The below query is able to split using "-" and reverse the order of score2 and compare to score1

SELECT CONCAT(
  SUBSTRING_INDEX(score1, '-', 1),
  SUBSTRING_INDEX(score1,'-',-1)
) AS my_score1,
CONCAT(
  SUBSTRING_INDEX(score2, '-', -1),
  SUBSTRING_INDEX(score2, '-', 1)
) AS my_score2
FROM test HAVING my_score1=my_score2

if explode is used together with foreach to build a new string you can simulate explode by using a while loop like this:

CREATE FUNCTION explode_and_loop(sep VARCHAR(),inputstring VARCHAR()) RETURNS VARCHAR() 
BEGIN
    DECLARE part,returnstring VARCHAR();
    DECLARE cnt,partsCnt INT();
    SET returnstring = '';
    SET partsCnt = ((LENGTH(inputstring ) - LENGTH(REPLACE(inputstring,sep,''))) DIV LENGTH(sep);
    SET cnt = 0;
    WHILE cnt <= partsCnt DO
        SET cnt = cnt + 1;
        SET part = SUBSTRING_INDEX(SUBSTRING_INDEX(inputstring ,sep,cnt),sep,-1);
        -- DO SOMETHING with the part eg make html:
        SET returnstring = CONCAT(returnstring,'<li>',part,'</li>')
    END WHILE;
    RETURN returnstring;
END

this example will return a html list of the parts. (required variable legths have to be added)

First of all you should change database structure - the score in this case is some kind of composite value and should be stored in two columns, eg. score_host, score_guest.

MySQL doesn't provide explode() equivalent however in this case you could use SUBSTRING() and LOCATE() to cut off score of a host and a guest.

SELECT 
   CONVERT(SUBSTRING(score, 1, LOCATE('-',score) - 2) USING INTEGER) as score_host,
   CONVERT(SUBSTRING(score, LOCATE('-',score)+2) USING INTEGER) as score_guest
FROM ...;

CONVERT() is used to cast a string "23" into number 23.

As @arman-p pointed out MYSQL has no explode(). However, I think the solution presented in much more complicated than it needs to be. To do a quick check when you are given a comma delimited list string (e.g, list of the table keys to look for) you do:

SELECT 
    table_key, field_1, field_2, field_3
FROM
    my_table
WHERE
    field_3 = 'my_field_3_value'
    AND (comma_list = table_key
        OR comma_list LIKE CONCAT(table_key, ',%')
        OR comma_list LIKE CONCAT('%,', table_key, ',%')
        OR comma_list LIKE CONCAT('%,', table_key))

This assumes that you need to also check field_3 on the table too. If you do not need it, do not add that condition.

This is actually a modified version of the selected answer in order to support Unicode characters but I don't have enough reputation to comment there.

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255) CHARSET utf8, delim VARCHAR(12), pos INT) RETURNS varchar(255) CHARSET utf8
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '')

The modifications are the following:

• The first parameter is set as utf8
• The function is set to return utf8
• The code uses CHAR_LENGTH() instead of LENGTH() to calculate the character length and not the byte length.

I try with SUBSTRING_INDEX(string,delimiter,count)

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'

see more on mysql.com http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index

I'm not sure if this is fully answering the question (it isn't), but it's the solution I came up with for my very similar problem. I know some of the other solutions look shorter but they seem to use SUBSTRING_INDEX() way more than necessary. Here I try to just use LOCATE() just once per delimiter.

-- *****************************************************************************
-- test_PVreplace

DROP FUNCTION IF EXISTS test_PVreplace;

delimiter //
CREATE FUNCTION test_PVreplace (
   str TEXT,   -- String to do search'n'replace on
   pv TEXT     -- Parameter/value pairs 'p1=v1|p2=v2|p3=v3'
   )
   RETURNS TEXT

-- Replace specific tags with specific values.

sproc:BEGIN
   DECLARE idx INT;
   DECLARE idx0 INT DEFAULT 1;   -- 1-origined, not 0-origined
   DECLARE len INT;
   DECLARE sPV TEXT;
   DECLARE iPV INT;
   DECLARE sP TEXT;
   DECLARE sV TEXT;

   -- P/V string *must* end with a delimiter.

   IF (RIGHT (pv, 1) <> '|') THEN
      SET pv = CONCAT (pv, '|');
      END IF;

   -- Find all the P/V pairs.

   SELECT LOCATE ('|', pv, idx0) INTO idx;
   WHILE (idx > 0) DO
      SET len = idx - idx0;
      SELECT SUBSTRING(pv, idx0, len) INTO sPV;

      -- Found a P/V pair.  Break it up.

      SELECT LOCATE ('=', sPV) INTO iPV;
      IF (iPV = 0) THEN
         SET sP = sPV;
         SET sV = '';
      ELSE
         SELECT SUBSTRING(sPV, 1, iPV-1) INTO sP;
         SELECT SUBSTRING(sPV, iPV+1) INTO sV;
         END IF;

      -- Do the substitution(s).

      SELECT REPLACE (str, sP, sV) INTO str;

      -- Do next P/V pair.

      SET idx0 = idx + 1;
      SELECT LOCATE ('|', pv, idx0) INTO idx;
      END WHILE;
   RETURN (str);
END//
delimiter ;

SELECT test_PVreplace ('%one% %two% %three%', '%one%=1|%two%=2|%three%=3');
SELECT test_PVreplace ('%one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', NULL);
SELECT test_PVreplace ('%one% %two% %three%', '%one%=%two%|%two%=%three%|%three%=III');

I faced same issue today and resolved it like below, please note in my case, I know the number of items in the concatenated string, hence I can recover them this way:

set @var1=0;
set @var2=0;
SELECT SUBSTRING_INDEX('value1,value2', ',', 1) into @var1;
SELECT SUBSTRING_INDEX('value1,value2', ',', -1) into @var2;

variables @var1 and @var2 would have the values similar to explode().