MULTI_SEARCH_ALL_POSITIONS

Description

The MULTI_SEARCH_ALL_POSITIONS function searches for multiple substrings in a string in batch. Returns an array containing the position of the first occurrence of each substring. The search is case-sensitive.

Syntax

MULTI_SEARCH_ALL_POSITIONS(<haystack>, <needles>)

Parameters

Parameter	Description
<haystack>	The target string to search in. Type: VARCHAR
<needles>	Array containing multiple substrings to search for. Type: ARRAY

Return Value

Returns ARRAY type, where the i-th element in the array represents the position of the first occurrence of the i-th substring in <needles> within <haystack>.

Special cases:

• Position counting starts from 1
• If substring is not found, the corresponding position returns 0
• Search is case-sensitive
• If <haystack> or <needles> is NULL, returns NULL
• Returns byte position, not the n-th character position

Examples

1. Basic usage: Search for multiple substrings

SELECT multi_search_all_positions('Hello, World!', ['Hello', 'World']);

+----------------------------------------------------------+
| multi_search_all_positions('Hello, World!', ['Hello', 'World']) |
+----------------------------------------------------------+
| [1, 8]                                                   |
+----------------------------------------------------------+

2. Case-sensitive: Lowercase not found

SELECT multi_search_all_positions('Hello, World!', ['hello', '!', 'world']);

+----------------------------------------------------------------------+
| multi_search_all_positions('Hello, World!', ['hello', '!', 'world']) |
+----------------------------------------------------------------------+
| [0, 13, 0]                                                           |
+----------------------------------------------------------------------+

3. Mixed search: Partially found

SELECT multi_search_all_positions('Hello, World!', ['Hello', '!', 'xyz']);

+--------------------------------------------------------------------+
| multi_search_all_positions('Hello, World!', ['Hello', '!', 'xyz']) |
+--------------------------------------------------------------------+
| [1, 13, 0]                                                         |
+--------------------------------------------------------------------+

4. Empty array

SELECT multi_search_all_positions('Hello', []);

+------------------------------------------+
| multi_search_all_positions('Hello', [])  |
+------------------------------------------+
| []                                       |
+------------------------------------------+

5. UTF-8 special character support

SELECT multi_search_all_positions('ṭṛì ḍḍumai Hello', ['ṭṛì', 'Hello', 'test']);

+----------------------------------------------------------------------------------------+
| multi_search_all_positions('ṭṛì ḍḍumai Hello', ['ṭṛì', 'Hello', 'test'])               |
+----------------------------------------------------------------------------------------+
| [1, 21, 0]                                                                             |
+----------------------------------------------------------------------------------------+

Keywords

MULTI_SEARCH,SEARCH,POSITIONS