MariaDB – Count the number of occurrences of a string

測試環境為 CentOS 8 x86_64 虛擬機.

有辦法計算字串 (如: This is MySQL , Mysql is Database) 裡面的字 (如: MySQL) 出現幾次嗎? 找不到相關函數.不過找到一個方式,參考文章 – https://www.tutorialspoint.com/count-the-number-of-occurrences-of-a-string-in-a-varchar-field-in-mysql

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server
  
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
   
MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
  
Database changed

建立測試用資料表.

create table StringOccurrenceDemo
(
 Cases varchar(100),
 StringValue varchar(500)
);

執行

MariaDB [testdb]> create table StringOccurrenceDemo
    -> (
    ->  Cases varchar(100),
    ->  StringValue varchar(500)
    -> );
Query OK, 0 rows affected (0.003 sec)

建立資料.

MariaDB [testdb]> insert into StringOccurrenceDemo values('First','This is MySQL Demo and MySQL is an open source RDBMS');
Query OK, 1 row affected (0.002 sec)

MariaDB [testdb]> insert into StringOccurrenceDemo values('Second','There is no');
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> insert into StringOccurrenceDemo values('Third','There is MySQL,Hi MySQL,Hello MySQL');
Query OK, 1 row affected (0.001 sec)

檢視資料.

MariaDB [testdb]> select *From StringOccurrenceDemo;
+--------+------------------------------------------------------+
| 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.000 sec)

利用下面方式來計算 MySQL 出現的次數.

SELECT Cases , StringValue,
ROUND ((LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") )) / LENGTH("MySQL")) AS NumberOfOccurrenceOfMySQL
from StringOccurrenceDemo;

執行

MariaDB [testdb]> SELECT Cases , StringValue,
    -> ROUND ((LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") )) / LENGTH("MySQL")) AS NumberOfOccurrenceOfMySQL
    -> from StringOccurrenceDemo;
+--------+------------------------------------------------------+---------------------------+
| Cases  | StringValue                                          | NumberOfOccurrenceOfMySQL |
+--------+------------------------------------------------------+---------------------------+
| First  | This is MySQL Demo and MySQL is an open source RDBMS |                         2 |
| Second | There is no                                          |                         0 |
| Third  | There is MySQL,Hi MySQL,Hello MySQL                  |                         3 |
+--------+------------------------------------------------------+---------------------------+
3 rows in set (0.000 sec)

SQL 語法說明:
先計算 總字串長度並減去所有不包含 MySQL 的字元.

LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") ))

計算 MYSQL 的長度,並除以剛算出已減去 MySQL 的字串長度.

(LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") )) / LENGTH("MySQL")

ROUND 取小數點,四捨五入.

ROUND ((LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") )) / LENGTH("MySQL")) 

即可算出 MySQL 在該字串出現了幾次.