MySQL Second (or third) Index Of in String

Question

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?

Madonnamadora

Answer 1

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.

Serpigo

Great explanation of how to get an index of an item by number. So if I have a csv with 8 items I can now get any of those 8 by the number....Very nice thanks much man. – Proceleusmatic

Answer 2

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

Ascocarp

Answer 3

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 ;

Honaker

Answer 4

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

Honaker

Answer 5

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

Electrodynamic

An index of -1 doesn't give you the third element, it gives you the last element. In the OP's example, -1 would return him EEE, not CCCC. – Evelyne