MySQL Second (or third) Index Of in String

mysql indexof
Bryan Field picture Bryan Field · Jan 15, 2013 · Viewed 27.6k times · Source

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?

Answer

Mike Brant picture Mike Brant · Jan 15, 2013

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.

Related questions

MySQL String Last Index Of

I am able to use LOCATE to get the index of a character such as a . in www.google.com. Thus I can use substring to strip out everything before the first .. Is there a way I can look for …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More