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

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

Equivalent of explode() to work with strings in MySQL

Related

Recent Posts