计算MySQL中VARCHAR字段中字符串出现的次数？
要计算VARCHAR中字符串出现的次数，我们可以使用长度减法。首先，我们将在create命令的帮助下创建一个表。
mysql> create table StringOccurrenceDemo -> ( -> Cases varchar(100), -> StringValue varchar(500) -> );
执行完上面的表后，我们将记录插入到表中。查询如下-
mysql> insert into StringOccurrenceDemo values('First','This is MySQL Demo and MySQL is an open source RDBMS');
mysql> insert into StringOccurrenceDemo values('Second','There is no');
mysql> insert into StringOccurrenceDemo values('Third','There is MySQL,Hi MySQL,Hello MySQL');在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'的计数。