I want to retrieve the last part of a large string in mysql after occurence of a particular substring. Please advice about how to do that.
For e.g. the main string contains "New Delhi is capitalof India". "Berlin is capitalof Germany".
I need to retrieve only India and Germany. I want know how to retrieve the data occuring after the specific substring like 'capitalof'.
I need to do this in mysql.
Use the SUBSTRING() function. The first argument is the string or the column name. The second argument is the index of the character at which the substring should begin. The third argument is the length of the substring.
MySQL LOCATE() Function The LOCATE() function returns the position of the first occurrence of a substring in a string. If the substring is not found within the original string, this function returns 0. This function performs a case-insensitive search.
The SUBSTRING() function extracts a substring from a string (starting at any position). Note: The SUBSTR() and MID() functions equals to the SUBSTRING() function.
You can use substring_index
select
substring_index(title,'capitalof',-1)
from table1
DEMO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With