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