How to query the number of occurrences of a certain string in a string in mysql

mysql query the number of occurrences of a certain string in the string

For example: Query the number of "ss" in the string "jjhshdgl'ddiiiisssddddjjsssjjssjj"

1. First query "ss "The length of ")

3. Replace "ss" in the string "jjhshdgl'ddiiiisssddddjjsssjjssjj" to be empty

replace("jjhshdgl'ddiiiisssddddjjsssjjssjj" "ss",""), and then calculate the replacement The length of

overall: select length(replace("jjhshdgl'ddiiiisssddddjjsssjjssjj""ss","")), recorded as len2

4. Then the result of the length subtraction is divided by "ss "The length of

(len2-len1)/(select length("ss"))

5. All overall sql:

SELECT
	diffCount / ( length( 'ss' ) ) 
FROM
	( SELECT ( length( 'jjhshdglddiiiisssddddjjsssjjssjj' ) - length( REPLACE ( 'jjhshdglddiiiisssddddjjsssjjssjj', 'ss', '' ) ) ) AS diffCount ) tmp;

Supplement: MySQL counts the number of character occurrences Implementation method (number of occurrences of mysql characters)

MySQL is a relational database management system with powerful functions for building complex database logic on web applications. MySQL provides many methods to help developers Solve data processing work. This article will explain how to use MySQL to count the number of character occurrences, and explain the implementation of the following character counting:

1. Use the MySQL function length()

The following example shows how to use the MySQL function length () Calculate the number of occurrences of a specific character:

SELECT 
 LENGTH() 
FROM
WHERE 
  LIKE ‘%%';

The above code will use the length() function to query the length of each string in the table, then add them, subtract the total length, and finally get the value you are looking for. The number of occurrences of the string in the table.

2. Use the MySQL function replace()

Another method is to use the MySQL function replace(). The following is a simple example:

SELECT 
 LENGTH() - LENGTH(REPLACE(, '', '')) 
FROM
  
WHERE 
  LIKE ‘%%';

The above code uses The replace() function finds the strings you are looking for, replaces them with null characters, and then calculates the number of times the string you are looking for appears in the table in the original string.

The above is the detailed content of How to query the number of occurrences of a certain string in a string in mysql. For more information, please follow other related articles on the PHP Chinese website!