Đếm số lần xuất hiện chuỗi trong cột VARCHAR MySQL?

Công thức: (LENGTH(str) - LENGTH(REPLACE(str, 'substr', ''))) / LENGTH('substr')

Tạo bảng

mysql> CREATE TABLE StringOccurrenceDemo (
    -> Cases VARCHAR(100),
    -> StringValue VARCHAR(500)
    -> );
Query OK, 0 rows affected (0.56 sec)

Chèn dữ liệu

mysql> INSERT INTO StringOccurrenceDemo VALUES
    -> ('First','This is MySQL Demo and MySQL is an open source RDBMS'),
    -> ('Second','There is no'),
    -> ('Third','There is MySQL,Hi MySQL,Hello MySQL');
Query OK, 3 rows affected

mysql> 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.00 sec)

Đếm từ 'MySQL'

mysql> 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.05 sec)