计一下 MySQL 中 VARCHAR 字段中某个字符串的出现次数

为了计算 VARCHAR 中某个字符串的出现次数，我们可以使用长度减法的逻辑。我们首先会借助创建命令来创建一个表。

mysql> create table StringOccurrenceDemo
   -> (
   -> Cases varchar(100),
   -> StringValue varchar(500)
   -> );
Query OK, 0 rows affected (0.56 sec)

执行完上述表格之后，我们会向表格中插入记录。查询如下 -

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)

借助 select 语句显示所有记录。

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)

以下是计算字符串“MySQL”出现次数的查询。结果会显示在列“NumberOfOccurrenceOfMySQL”中

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                                             |                         0 |
| Third  | There is MySQL,Hi MySQL,Hello MySQL                  |                         3 |
+--------+------------------------------------------------------+---------------------------+
3 rows in set (0.05 sec)

上述输出表明我们已经找到了字符串“MySQL”出现次数。

更新于: 2019 年 7 月 30 日

4K+浏览

开启你的职业生涯

完成本课程以取得认证

开始
广告