Number of times a search string is present inside a main string

There 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 .
https://www.plus2net.com/sql_tutorial/group_concat.php
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()
SELECT CHARACTER_LENGTH("https://www.plus2net.com/sql_tutorial/group_concat.php")
Output is 54

Step 2 : Now we will use REPLACE command to remove '/' from the string
SELECT REPLACE("https://www.plus2net.com/sql_tutorial/group_concat.php",'/','')
Step 3 : We will count the number of characters present in the string after the replacement.
SELECT CHARACTER_LENGTH(REPLACE("https://www.plus2net.com/sql_tutorial/group_concat.php",'/',''))
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 .
SELECT 
CHARACTER_LENGTH("https://www.plus2net.com/sql_tutorial/group_concat.php")  - 
CHARACTER_LENGTH(REPLACE("https://www.plus2net.com/sql_tutorial/group_concat.php",'/',''))
Output is 4

Now let us change the query to find out number of DOTS ( . ) used inside the string.
SELECT 
CHARACTER_LENGTH("https://www.plus2net.com/sql_tutorial/group_concat.php")  - 
CHARACTER_LENGTH(REPLACE("https://www.plus2net.com/sql_tutorial/group_concat.php",'.',''))
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.
SELECT (
CHARACTER_LENGTH("https://www.plus2net.com/sql_tutorial/sql_count.php")  - 
CHARACTER_LENGTH(REPLACE("https://www.plus2net.com/sql_tutorial/sql_count.php",'sql',''))
) / CHARACTER_LENGTH('sql')
Output is 2

Using in a table data

We will collect names from our student table where our search string 'John' is present.

SELECT * FROM `student` WHERE 
(
CHARACTER_LENGTH(name)  - 
CHARACTER_LENGTH(REPLACE(name,'John',''))
) / CHARACTER_LENGTH('John') >0
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
Your Rating




Post Comment This is for short comments only. Use the forum for more discussions.








HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer