What would be the simplest way to locate the index of the third space in a string.
My goal is to get CCC out 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.
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?
You would want to use SUBSTRING_INDEX function like this
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(field, ' ', 3), ' ', -1)
FROM table
The inner function call would get you to AAAA BBBB CCCC while the outer function call would pare that down to just CCCC.
Generally you can select the nth word in a string using:
SET @N = 3; -- 3rd word
SET @delimiter = ' ';
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(words, @delimiter, @N), @delimiter, -1)
FROM
my_table
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 ;
use below query to find any random id from table after group by. Here id is the 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
| Id | Name | Department |
|---|---|---|
| 1 | Amit Kumar Sharma | Computer Science |
*You can extract third string by simple query
Query :-
SELECT SUBSTRING_INDEX(Name, ' ', -1) as last_name FROM table_name
Output :- Sharma
