Skip to main content
Skip to main content

COUNT_SUBSTRINGS

count_substrings

description

Syntax

int count_substrings(STRING str, STRING pattern) Returns the total number of occurrences of the substring pattern in the string str. Note: The current implementation shifts by the length of the pattern after each match in the string. Therefore, when str: ccc and pattern: cc, the result returned is 1.

Arguments

str — The string to be checked. Type: String pattern — The substring to be matched. Type: String

Returned value(s)

Returns the total number of occurrences of the substring.

example

mysql [(none)]>select count_substrings('a1b1c1d','1');
+----------------------------------+
| count_substrings('a1b1c1d', '1') |
+----------------------------------+
| 3 |
+----------------------------------+

mysql [(none)]>select count_substrings(',,a,b,c,',',');
+-----------------------------------+
| count_substrings(',,a,b,c,', ',') |
+-----------------------------------+
| 5 |
+-----------------------------------+

mysql [(none)]>select count_substrings('ccc','cc');
+--------------------------------+
| count_substrings('ccc', 'cc') |
+--------------------------------+
| 1 |
+--------------------------------+

mysql [(none)]>SELECT count_substrings(NULL,',');
+-----------------------------+
| count_substrings(NULL, ',') |
+-----------------------------+
| NULL |
+-----------------------------+

mysql [(none)]>select count_substrings('a,b,c,abcde','');
+-------------------------------------+
| count_substrings('a,b,c,abcde', '') |
+-------------------------------------+
| 0 |
+-------------------------------------+

mysql [(none)]>select count_substrings(NULL, 'a');
+-----------------------------+
| count_substrings(NULL, 'a') |
+-----------------------------+
| NULL |
+-----------------------------+

mysql [(none)]>select count_substrings('','asd');
+-----------------------------+
| count_substrings('', 'asd') |
+-----------------------------+
| 0 |
+-----------------------------+

mysql [(none)]>select count_substrings('abccbaacb','c');
+------------------------------------+
| count_substrings('abccbaacb', 'c') |
+------------------------------------+
| 3 |
+------------------------------------+

keywords

COUNT_SUBSTRINGS,SUBSTRINGS