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>)

Parameters

Parameter	Description
<str>	The string to be searched. Type: STRING
<pattern>	The substring to match. Type: STRING

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

Examples

1. Basic usage

SELECT count_substrings('a1b1c1d', '1');

+----------------------------------+
| count_substrings('a1b1c1d', '1') |
+----------------------------------+
|                                3 |
+----------------------------------+

2. Case with consecutive commas

SELECT count_substrings(',,a,b,c,', ',');

+-----------------------------------+
| count_substrings(',,a,b,c,', ',') |
+-----------------------------------+
|                                 5 |
+-----------------------------------+

3. Case with overlapping substrings

SELECT count_substrings('ccc', 'cc');

+--------------------------------+
| count_substrings('ccc', 'cc')  |
+--------------------------------+
|                              1 |
+--------------------------------+

4. NULL value handling

SELECT count_substrings(NULL, ',');

+-----------------------------+
| count_substrings(NULL, ',') |
+-----------------------------+
|                        NULL |
+-----------------------------+

5. Empty string handling

SELECT count_substrings('a,b,c,abcde', '');

+-------------------------------------+
| count_substrings('a,b,c,abcde', '') |
+-------------------------------------+
|                                   0 |
+-------------------------------------+