COUNT_SUBSTRINGS
Description
The COUNT_SUBSTRINGS function counts the number of occurrences of a specified substring within a string. Note: The current implementation continues searching after shifting by the length of the substring when a match is found. For example, when str='ccc' and pattern='cc', the result returned is 1.
Syntax
COUNT_SUBSTRINGS(<str>, <pattern>[, <start_pos>])
Parameters
| Parameter | Description |
|---|---|
<str> | The string to be searched. Type: STRING |
<pattern> | The substring to match. Type: STRING |
<start_pos> | Position (1-based) at which the search starts. Type: INT. Optional |
Return Value
Returns an INT type, representing the number of times the substring appears in the string.
Special cases:
- If str is NULL, returns NULL
- If pattern is an empty string, returns 0
- If str is an empty string, returns 0
- If start_pos is less than or equal to 0 or exceeds the string length, returns 0
Examples
- Basic usage
SELECT count_substrings('a1b1c1d', '1');
+----------------------------------+
| count_substrings('a1b1c1d', '1') |
+----------------------------------+
| 3 |
+----------------------------------+
- Case with consecutive commas
SELECT count_substrings(',,a,b,c,', ',');
+-----------------------------------+
| count_substrings(',,a,b,c,', ',') |
+-----------------------------------+
| 5 |
+-----------------------------------+
- Case with overlapping substrings
SELECT count_substrings('ccc', 'cc');
+--------------------------------+
| count_substrings('ccc', 'cc') |
+--------------------------------+
| 1 |
+--------------------------------+
- NULL value handling
SELECT count_substrings(NULL, ',');
+-----------------------------+
| count_substrings(NULL, ',') |
+-----------------------------+
| NULL |
+-----------------------------+
- Empty string handling
SELECT count_substrings('a,b,c,abcde', '');
+-------------------------------------+
| count_substrings('a,b,c,abcde', '') |
+-------------------------------------+
| 0 |
+-------------------------------------+
- Using the start position parameter
SELECT count_substrings('ṭṛì ḍḍumai ṭṛì ti ḍḍumannàri', 'ḍḍu', 1),
count_substrings('ṭṛì ḍḍumai ṭṛì ti ḍḍumannàri', 'ḍḍu', 6);
+-----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+
| count_substrings('ṭṛì ḍḍumai ṭṛì ti ḍḍumannàri', 'ḍḍu', 1) | count_substrings('ṭṛì ḍḍumai ṭṛì ti ḍḍumannàri', 'ḍḍu', 6) |
+-----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+
| 2 | 1 |
+-----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+
- Start position out of range
SELECT count_substrings('éèêëìíîïðñòó éèêëìíîïðñòó', 'éèê', 0),
count_substrings('éèêëìíîïðñòó éèêëìíîïðñòó', 'éèê', 30);
+------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| count_substrings('éèêëìíîïðñòó éèêëìíîïðñòó', 'éèê', 0) | count_substrings('éèêëìíîïðñòó éèêëìíîïðñòó', 'éèê', 30) |
+------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| 0 | 0 |
+------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
- Start position out of range
SELECT count_substrings('你好，世界！你好，世界！', '世界', 0),
count_substrings('你好，世界！你好，世界！', '世界', 30);
+-----------------------------------------------------------------------+------------------------------------------------------------------------+
| count_substrings('你好，世界！你好，世界！', '世界', 0) | count_substrings('你好，世界！你好，世界！', '世界', 30) |
+-----------------------------------------------------------------------+------------------------------------------------------------------------+
| 0 | 0 |
+-----------------------------------------------------------------------+------------------------------------------------------------------------+
