Equivalent of explode to work with strings in MySQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt       THEN       SET pv   CONCAT  pv              END IF      -- Find all the P V pairs      SELECT LOCATE       pv  idx0  INTO idx     WHILE  idx  gt  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

User · Answer

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

User · Answer

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

User · Answer

I try with SUBSTRING INDEX string delimiter count   mysql gt  SELECT SUBSTRING INDEX  www mysql com        2   - gt   www mysql   mysql gt  SELECT SUBSTRING INDEX  www mysql com        -2   - gt   mysql com    see more on mysql com http   dev mysql com doc refman 5 1 en string-functions html function substring-index

User · Answer

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  lt   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   lt li gt   part   lt  li gt        END WHILE      RETURN returnstring  END   this example will return a html list of the parts   required variable legths have to be added

User · Answer

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

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

Examples related to mysql

Examples related to sql