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 by Angela on August 11, 2011 - 3:25 pm
How would I alter this to select the 2nd last word in the field?
#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.