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 woul开发者_Go百科d need to be opponent1.score backwards).

MYSQL has no explode() like function built in. But you can easily add similar function to your DB and then use it from php queries. That function will look like:

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

Usage:

SELECT SPLIT_STRING('apple, pear, melon', ',', 1)

The example above will return apple. I think that it will be impossible to return array in MySQL so you must specify which occurrence to return explicitly in pos. Let me know if you succeed using it.

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

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;
  

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.

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.

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

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.

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.

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)

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().

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 believe your problem can be solved in other simpler ways. But to answer your question, you can leverage the JSON functions in order to "explode" a string.

In your particular case, the strings '7 - 31' and '31 - 7' can be reformatted into a valid JSON string combining functions CONCAT() and REPLACE():

SELECT CONCAT('["',REPLACE('31 - 7',' - ','","'),'"]') score

The output will look like this:

["31","7"]

This is a valid JSON string.

The next step is to use the JSON_VALUE() function to extract each opponents score.

SELECT JSON_VALUE(CONCAT('["',REPLACE('31 - 7',' - ','","'),'"]'),'$[0]') oponent1, JSON_VALUE(CONCAT('["',REPLACE('31 - 7',' - ','","'),'"]'),'$[1]') oponent2

The result will look like this:

+-----------+----------+
| oponent1  | oponent2 |
+-----------+----------+
|       31  |        7 |
+-----------+----------+

Finally, you can manipulate these values as you please. Perhaps you wish to reorder them, placing the least score first so that the score is always a standard format instead of being dependent on who the home team may be.