MySQL 字符串中的第二（或第三）索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14347581/
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
MySQL Second (or third) Index Of in String
提问by Bryan Field
What would be the simplest way to locate the index of the third space in a string.
在字符串中定位第三个空格的索引的最简单方法是什么。
My goal is to get CCCout of this space separated list: AAAA BBBB CCCC DDDD EEE. where A and B and D are fixed length, and C is variable length, E F G are optional.
我的目标是CCC摆脱这个空格分隔的列表：AAAA BBBB CCCC DDDD EEE. 其中 A 和 B 和 D 是固定长度，C 是可变长度，EFG 是可选的。
In Java I would use indexof, with a starting point of 10 and that would get me the third space, but it seems that I cannot do that in MySQL, so I thought maybe I could find a 'third index of' function?
在 Java 中，我会使用 indexof，起点为 10，这会给我第三个空间，但似乎我不能在 MySQL 中做到这一点，所以我想也许我可以找到“第三个索引”函数？
回答by Mike Brant
You would want to use SUBSTRING_INDEXfunction like this
你会想要使用这样的SUBSTRING_INDEX功能
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(field, ' ', 3), ' ', -1)
FROM table
The inner function call would get you to AAAA BBBB CCCCwhile the outer function call would pare that down to just CCCC.
内部函数调用将使您能够使用，AAAA BBBB CCCC而外部函数调用会将其缩减为CCCC.
回答by Kermit
You could use SUBSTRING_INDEX.
你可以使用SUBSTRING_INDEX.
回答by Nae
Generally you can select the nthword in a string using:
通常，您可以nth使用以下方法选择字符串中的单词：
SET @N = 3; -- 3rd word
SET @delimiter = ' ';
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(words, @delimiter, @N), @delimiter, -1)
FROM
my_table
回答by nierjesh kumar
DROP FUNCTION IF EXISTS `Find_string_by_position`$$
CREATE DEFINER=`root`@`localhost` FUNCTION
`Find_string_by_position`(str VARCHAR(255), delimeter VARCHAR(255),pos INT(2)) RETURNS VARCHAR(255) CHARSET utf8mb4 BEGIN
DECLARE s VARCHAR(255);
DECLARE d VARCHAR(255);
DECLARE p INT DEFAULT 1;
DECLARE val VARCHAR(255);
SET s = LCASE(str);
SET d = delimeter;
SET p = pos;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s,d,p),d,-1) INTO @val;
RETURN @val;
END$$
DELIMITER ;
回答by nierjesh kumar
use below query to find any random id from table after group by. Here id is the autoincrement_id.
使用以下查询在分组后从表中查找任何随机 id。这里的 id 是 autoincrement_id。
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(id),",",FLOOR(RAND()*COUNT(DISTINCT id))+1),",",-1) AS random_id FROM tableName GROUP BY groupbyColumn
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(id),",",FLOOR(RAND()*COUNT(DISTINCT id))+1),",",-1) AS random_id FROM tableName GROUP BY groupbyColumn
