等效于explode()以在MySQL中使用字符串 [英] 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),
       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.

这篇关于等效于explode()以在MySQL中使用字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！