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.