Get the nth Word from a text field in mysql


I had a requirement to get the nth word out of a string and sort or order by the same. This is used when the text is stored in a purticular format and you would want to order it according to that or group it or so on.

So how is it done?

We do it using a combination of SUBSTRING,REPLACE,LENGTH,SUBSTRING_INDEX functions available in MYSQL

So here is the query for it:

 select Name, REPLACE(SUBSTRING(SUBSTRING_INDEX(Name, ' ', 2),  LENGTH(SUBSTRING_INDEX(Name, ' ', 2 -1)) + 1), ' ', '') as second from  CountryNames; 

Here I have selected 2nd word from Name column from CountryNames table. If you change the “2” in the query to any other number higher than 1 then you would get that nth word. The ‘ ‘ represents delimiter. You can use any other delimiters by changing the same.

Also there is a function method of writing the same.  Here is how that is written:


CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, ''); 

You call the function as follows:


SELECT SPLIT_STR(Name, ‘ ‘, 3) as third from CountryNames;


So that’s it. If you have any issues or different method of writing the command then put as comment.


, , , , ,

  1. #1 by Angela on August 11, 2011 - 3:25 pm

    How would I alter this to select the 2nd last word in the field?

  2. #2 by Andy on May 8, 2012 - 11:44 pm

    I noticed this method sometimes for some unknown reason clips the first character of the words. A more elegant solution would be this:

    SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(Name, ” “, 4) , ” “, -1) as third from `your_table`

    Where the number ‘4’ denotes the fourth word. This could also easily be reversed to answear Angelas question :

    SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(Name, ” “, -2) , ” “, 1) as third from `your_table`

    This will get the second last word.

  3. #3 by swathi on September 6, 2013 - 8:38 am

    i need to display second string in the employee name if name have two or more words.how can i?

(will not be published)