MySQL 相当于explode() 在MySQL 中处理字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5928599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Equivalent of explode() to work with strings in MySQL
提问by Bob Cavezza
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?
在 MySQL 中，我希望能够搜索'31 - 7', 当另一个值 = 时'7 - 31'。我会用什么语法来拆分 MySQL 中的字符串？在 PHP 中，我可能会使用explode(' - ',$string)它们并将它们放在一起。有没有办法在 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:
理想的 MySQL 调用是：
Where opponent1.date = opponent2.date
AND opponent1.score = opponent2.score
(opponent2.scorewould need to be opponent1.scorebackwards).
（opponent2.score需要opponent1.score向后）。
回答by Arman P.
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:
MYSQL 没有explode()内置类似功能。但是您可以轻松地将类似功能添加到您的数据库中，然后从 php 查询中使用它。该函数将如下所示：
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, '');
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.
上面的例子将返回apple. 我认为在 MySQL 中返回数组是不可能的，因此您必须指定在pos. 如果您成功使用它，请告诉我。
回答by madde74
I try with SUBSTRING_INDEX(string,delimiter,count)
我试试 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
在 mysql.com 上查看更多信息 http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index
回答by Disha Goyal
You can use stored procedure in this way..
您可以通过这种方式使用存储过程..
DELIMITER |
CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)
BEGIN
DROP TABLE IF EXISTS temp_explode;
CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));
SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');
PREPARE myStmt FROM @sql;
EXECUTE myStmt;
END |
DELIMITER ;
example call:
SET @str = "The quick brown fox jumped over the lazy dog"; SET @delim = " "; CALL explode(@delim,@str); SELECT id,word FROM temp_explode;
示例调用：
SET @str = "The quick brown fox jumped over the lazy dog"; SET @delim = " "; CALL explode(@delim,@str); SELECT id,word FROM temp_explode;
回答by Crozin
First of all you should change database structure - the score in this case is some kind of composite value and should be stored in two columns, eg. score_host, score_guest.
首先，您应该更改数据库结构 - 在这种情况下，分数是某种复合值，应存储在两列中，例如。score_host, score_guest.
MySQL doesn't provide explode()equivalent however in this case you could use SUBSTRING()and LOCATE()to cut off score of a host and a guest.
MySQL 不提供explode()等价物，但是在这种情况下，您可以使用SUBSTRING()和LOCATE()来切断主机和来宾的分数。
SELECT
CONVERT(SUBSTRING(score, 1, LOCATE('-',score) - 2) USING INTEGER) as score_host,
CONVERT(SUBSTRING(score, LOCATE('-',score)+2) USING INTEGER) as score_guest
FROM ...;
CONVERT()is used to cast a string "23"into number 23.
CONVERT()用于将字符串"23"转换为 number 23。
回答by Sergio Rodriguez
Use this function. It works like a charm. replace "|" with the char to explode/split and the values 1,2,3,etc are based on the number of entries in the data-set: Value_ONE|Value_TWO|Value_THREE.
使用此功能。它就像一个魅力。替换“|” 使用要分解/拆分的字符和值 1、2、3 等基于数据集中的条目数：Value_ONE|Value_TWO|Value_THREE。
SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 1), '|', -1) AS PSI,
SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 2), '|', -1) AS GPM,
SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 3), '|', -1) AS LIQUID
I hope this helps.
我希望这有帮助。
回答by philip mudenyo
use substring_index, in the example below i have created a table with column score1 and score2, score1 has 3-7, score2 7-3 etc as shown in the image. The below query is able to split using "-" and reverse the order of score2 and compare to score1
使用 substring_index，在下面的示例中，我创建了一个包含列 score1 和 score2 的表，score1 有 3-7，score2 7-3 等，如图所示。下面的查询可以使用“-”进行拆分并反转 score2 的顺序并与 score1 进行比较
SELECT CONCAT(
SUBSTRING_INDEX(score1, '-', 1),
SUBSTRING_INDEX(score1,'-',-1)
) AS my_score1,
CONCAT(
SUBSTRING_INDEX(score2, '-', -1),
SUBSTRING_INDEX(score2, '-', 1)
) AS my_score2
FROM test HAVING my_score1=my_score2
回答by Al Zziwa
As @arman-p pointed out MYSQL has no explode(). However, I think the solution presented in much more complicated than it needs to be. To do a quick check when you are given a comma delimited list string (e.g, list of the table keys to look for) you do:
正如@arman-p 指出的那样，MYSQL 没有explode()。但是，我认为所提出的解决方案比它需要的要复杂得多。要在获得逗号分隔的列表字符串（例如，要查找的表键列表）时进行快速检查，请执行以下操作：
SELECT
table_key, field_1, field_2, field_3
FROM
my_table
WHERE
field_3 = 'my_field_3_value'
AND (comma_list = table_key
OR comma_list LIKE CONCAT(table_key, ',%')
OR comma_list LIKE CONCAT('%,', table_key, ',%')
OR comma_list LIKE CONCAT('%,', table_key))
This assumes that you need to also check field_3 on the table too. If you do not need it, do not add that condition.
这假设您还需要检查表上的 field_3。如果您不需要它，请不要添加该条件。
回答by ohjay
if explode is used together with foreach to build a new string you can simulate explode by using a while loop like this:
如果explode与foreach一起使用来构建一个新的字符串，你可以使用像这样的while循环来模拟explode：
CREATE FUNCTION explode_and_loop(sep VARCHAR(),inputstring VARCHAR()) RETURNS VARCHAR()
BEGIN
DECLARE part,returnstring VARCHAR();
DECLARE cnt,partsCnt INT();
SET returnstring = '';
SET partsCnt = ((LENGTH(inputstring ) - LENGTH(REPLACE(inputstring,sep,''))) DIV LENGTH(sep);
SET cnt = 0;
WHILE cnt <= partsCnt DO
SET cnt = cnt + 1;
SET part = SUBSTRING_INDEX(SUBSTRING_INDEX(inputstring ,sep,cnt),sep,-1);
-- DO SOMETHING with the part eg make html:
SET returnstring = CONCAT(returnstring,'<li>',part,'</li>')
END WHILE;
RETURN returnstring;
END
this example will return a html list of the parts. (required variable legths have to be added)
此示例将返回部件的 html 列表。（必须添加所需的可变长度）
回答by vensires
This is actually a modified version of the selected answer in order to support Unicode characters but I don't have enough reputation to comment there.
这实际上是所选答案的修改版本，以支持 Unicode 字符，但我没有足够的声誉在那里发表评论。
CREATE FUNCTION SPLIT_STRING(str VARCHAR(255) CHARSET utf8, delim VARCHAR(12), pos INT) RETURNS varchar(255) CHARSET utf8
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
delim, '')
The modifications are the following:
修改如下：
- The first parameter is set as
utf8 - The function is set to return
utf8 - The code uses
CHAR_LENGTH()instead ofLENGTH()to calculate the character lengthand notthe byte length.
- 第一个参数设置为
utf8 - 函数设置为返回
utf8 - 该代码使用
CHAR_LENGTH()，而不是LENGTH()计算字符长度和不该字节长度。
回答by Alan Stewart
I'm not sure if this is fully answering the question (it isn't), but it's the solution I came up with for my very similar problem. I know some of the other solutions look shorter but they seem to use SUBSTRING_INDEX() way more than necessary. Here I try to just use LOCATE() just once per delimiter.
我不确定这是否完全回答了这个问题（不是），但这是我为我的非常相似的问题提出的解决方案。我知道其他一些解决方案看起来更短，但它们似乎不必要地使用 SUBSTRING_INDEX() 方式。在这里，我尝试对每个分隔符仅使用一次 LOCATE()。
-- *****************************************************************************
-- test_PVreplace
DROP FUNCTION IF EXISTS test_PVreplace;
delimiter //
CREATE FUNCTION test_PVreplace (
str TEXT, -- String to do search'n'replace on
pv TEXT -- Parameter/value pairs 'p1=v1|p2=v2|p3=v3'
)
RETURNS TEXT
-- Replace specific tags with specific values.
sproc:BEGIN
DECLARE idx INT;
DECLARE idx0 INT DEFAULT 1; -- 1-origined, not 0-origined
DECLARE len INT;
DECLARE sPV TEXT;
DECLARE iPV INT;
DECLARE sP TEXT;
DECLARE sV TEXT;
-- P/V string *must* end with a delimiter.
IF (RIGHT (pv, 1) <> '|') THEN
SET pv = CONCAT (pv, '|');
END IF;
-- Find all the P/V pairs.
SELECT LOCATE ('|', pv, idx0) INTO idx;
WHILE (idx > 0) DO
SET len = idx - idx0;
SELECT SUBSTRING(pv, idx0, len) INTO sPV;
-- Found a P/V pair. Break it up.
SELECT LOCATE ('=', sPV) INTO iPV;
IF (iPV = 0) THEN
SET sP = sPV;
SET sV = '';
ELSE
SELECT SUBSTRING(sPV, 1, iPV-1) INTO sP;
SELECT SUBSTRING(sPV, iPV+1) INTO sV;
END IF;
-- Do the substitution(s).
SELECT REPLACE (str, sP, sV) INTO str;
-- Do next P/V pair.
SET idx0 = idx + 1;
SELECT LOCATE ('|', pv, idx0) INTO idx;
END WHILE;
RETURN (str);
END//
delimiter ;
SELECT test_PVreplace ('%one% %two% %three%', '%one%=1|%two%=2|%three%=3');
SELECT test_PVreplace ('%one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', NULL);
SELECT test_PVreplace ('%one% %two% %three%', '%one%=%two%|%two%=%three%|%three%=III');
