Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Count the number of occurrences of a string in a VARCHAR field in MySQL?
To count the number of occurrences of a string in a VARCHAR, we can use the logic of subtraction with length. First, we will create a table with the help of create command.
mysql> create table StringOccurrenceDemo -> ( -> Cases varchar(100), -> StringValue varchar(500) -> ); Query OK, 0 rows affected (0.56 sec)
After executing the above table, we will insert records into the table. The query is as follows −
mysql> insert into StringOccurrenceDemo values('First','This is MySQL Demo and MySQL is an open source RDBMS');
Query OK, 1 row affected (0.07 sec)
mysql> insert into StringOccurrenceDemo values('Second','There is no');
Query OK, 1 row affected (0.20 sec)
mysql> insert into StringOccurrenceDemo values('Third','There is MySQL,Hi MySQL,Hello MySQL');
Query OK, 1 row affected (0.17 sec)
Display all records with the help of select statement.
mysql> select *From StringOccurrenceDemo;
The following is the output.
+--------+------------------------------------------------------+ | Cases | StringValue | +--------+------------------------------------------------------+ | First | This is MySQL Demo and MySQL is an open source RDBMS | | Second | There is no | | Third | There is MySQL,Hi MySQL,Hello MySQL | +--------+------------------------------------------------------+ 3 rows in set (0.00 sec)
The following is the query to count the occurrences of string “MySQL”. The result will get displayed in the column ‘NumberOfOccurrenceOfMySQL’
mysql> SELECT Cases,StringValue,
-> ROUND (
-> (
-> LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") )
-> ) / LENGTH("MySQL")
-> ) AS NumberOfOccurrenceOfMySQL
-> from StringOccurrenceDemo;
Here is the output.
+--------+------------------------------------------------------+---------------------------+ | Cases | StringValue | NumberOfOccurrenceOfMySQL| +--------+------------------------------------------------------+---------------------------+ | First | This is MySQL Demo and MySQL is an open source RDBMS | 2 | | Second | There is | 0 | | Third | There is MySQL,Hi MySQL,Hello MySQL | 3 | +--------+------------------------------------------------------+---------------------------+ 3 rows in set (0.05 sec)
The above output shows that we have found the count for occurrences of string ‘MySQL’.
Updated on: 30-Jul-2019
4K+ Views
Advertisements