等价于在 MySQL 中处理字符串的 explode() [英] Equivalent of explode() to work with strings in MySQL

问题描述

在 MySQL 中，当另一个值 = '7 - 31' 时，我希望能够搜索 '31 - 7'.我将使用什么语法来拆分 MySQL 中的字符串?在 PHP 中，我可能会使用 explode(' - ',$string) 并将它们放在一起.有没有办法在 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.

理想的 MySQL 调用应该是:

The ideal MySQL call would be:

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

(opponent2.score 需要向后 opponent1.score).

推荐答案

MYSQL 没有内置类似 explode() 的函数.但是您可以轻松地将类似的函数添加到您的数据库中，然后从php 查询.该函数将如下所示:

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, '');

用法:

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

上面的例子将返回apple.我认为在 MySQL 中返回数组是不可能的，因此您必须在 pos 中明确指定要返回的事件.如果你成功使用它，请告诉我.

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 中处理字符串的 explode()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！