MySQL- SUBSTRING_INDEX – Select Patterns
Consider, a MySQL table having values in a column like below:
SELECT location FROM geo LIMIT 3;
“location”
“India.Karnataka.Shimoga.Gopala”
“India.Karnataka.Bengaluru.BTM”
“India.Karnataka.Chikmaglore.Koppa”
“India.Karnataka.Shimoga.Gopala”
“India.Karnataka.Bengaluru.BTM”
“India.Karnataka.Chikmaglore.Koppa”
My requirement is to take only 4th value from each of the rows(such as, Gopala,BTM,Koppa).
I don’t want to display remaining values.
Its same as what ‘cut’ command will do in Linux.
For this, we can use SUBSTRING_INDEX function.
SELECT SUBSTRING_INDEX(location,’.’,-1) from geo LIMIT 3;
“location”
“Gopala”
“BTM”
“Koppa”
Syntax: SUBSTRING_INDEX(string,delimiter,count)
Here count means column number based on delimiter.
Negative value indicates that the column numbers calculated from right side.
So, if I give ‘-2’ instead of ‘-1’:
SELECT SUBSTRING_INDEX(location,’.’,-2) from geo LIMIT 3;
“location”
“Shimoga.Gopala”
“Bengaluru.BTM”
“Chikmaglore.Koppa”