相当于explode() 在MySQL 中处理字符串

2021-11-20 00:00:00 sql 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 向后).

(opponent2.score would need to be opponent1.score backwards).

推荐答案

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.

相关文章