Number of times a search string is present inside a main stringThere is no function in MySQL to return directly the number of times a string present inside another string.
Counting presence of single character string.Our string is here .
How many forward slash ( / ) present in above string
Step 1 : Let us find out number of character present in the string by using CHARACTER_LENGTH()
Output is 54
Step 2 : Now we will use REPLACE command to remove '/' from the string
Step 3 : We will count the number of characters present in the string after the replacement.
Output is 50.
Step 4 : The difference between previous calculation ( Step 1 ) and present calculation ( Step 3 ) is the number of ‘/’ present in the string. WE will combine all into one query .
Output is 4
Now let us change the query to find out number of DOTS ( . ) used inside the string.
Output is 3
To get the number of occurrence of a search string ,we have used the difference between length of the string before removing the search string and after removing the search string.
Searching for a word of more than one character length.Directly we can’t apply the above technique to find out number of occurrence of a search string of more than one character length. We need to divide the difference with the length of the search string to get the number of occurrences of the search sting.
We will search for the sring 'sql' inside another main string.
Output is 2
Using in a table dataWe will collect names from our student table where our search string 'John' is present.
We will get a list of names with 'John' inside them.
This is only an example to show how to count number of occurrence of a string and this is not a better way to filter records based on matching conditions. To get records with condition better to use LIKE query