Counting number of presence of a search sting inside a main string in MYSQL query

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 string '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

SQL to get length of string
elt(): String from the postion

All String Functions LENGTH()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

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

Counting presence of single character string.

Searching for a word of more than one character length.

Using in a table data

Subhendu Mohapatra