問題描述

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

這篇關于相當于explode() 在MySQL 中處理字符串的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！