STARTS_WITH
Description
The STARTS_WITH function checks if a string starts with a specified prefix. This is a boolean function that performs exact prefix matching and is case-sensitive.
Syntax
STARTS_WITH(<str>, <prefix>)
Parameters
| Parameter | Description |
|---|---|
<str> | The main string to check. Type: VARCHAR |
<prefix> | The prefix string to match. Type: VARCHAR |
Return Value
Returns BOOLEAN type (displayed as TINYINT in Doris, 1 for true, 0 for false).
Matching rules:
- Exact prefix match, case-sensitive
- Empty prefix matches any string (returns true)
- Supports correct matching of UTF-8 multi-byte characters
- Prefix length cannot exceed main string length (unless prefix is empty)
Special cases:
- Returns NULL if any argument is NULL
- Returns true if prefix is empty string (any string starts with empty string)
- Returns false if main string is empty but prefix is not
- Returns true if both are empty strings
Examples
- Basic prefix matching
SELECT STARTS_WITH('hello world', 'hello'), STARTS_WITH('hello world', 'world');
+-------------------------------------+-------------------------------------+
| STARTS_WITH('hello world', 'hello') | STARTS_WITH('hello world', 'world') |
+-------------------------------------+-------------------------------------+
| 1 | 0 |
+-------------------------------------+-------------------------------------+
- Case sensitivity
SELECT STARTS_WITH('Hello World', 'hello'), STARTS_WITH('Hello World', 'Hello');
+-------------------------------------+-------------------------------------+
| STARTS_WITH('Hello World', 'hello') | STARTS_WITH('Hello World', 'Hello') |
+-------------------------------------+-------------------------------------+
| 0 | 1 |
+-------------------------------------+-------------------------------------+
- NULL value handling
SELECT STARTS_WITH(NULL, 'test'), STARTS_WITH('test', NULL);
+----------------------------+----------------------------+
| STARTS_WITH(NULL, 'test') | STARTS_WITH('test', NULL) |
+----------------------------+----------------------------+
| NULL | NULL |
+----------------------------+----------------------------+
- Empty string handling
SELECT STARTS_WITH('hello', ''), STARTS_WITH('', 'world');
+---------------------------+----------------------------+
| STARTS_WITH('hello', '') | STARTS_WITH('', 'world') |
+---------------------------+----------------------------+
| 1 | 0 |
+---------------------------+----------------------------+
- Complete string match
SELECT STARTS_WITH('test', 'test'), STARTS_WITH('test', 'testing');
+-----------------------------+--------------------------------+
| STARTS_WITH('test', 'test') | STARTS_WITH('test', 'testing') |
+-----------------------------+--------------------------------+
| 1 | 0 |
+-----------------------------+--------------------------------+
- File path checking
SELECT STARTS_WITH('/home/user/file.txt', '/home'), STARTS_WITH('C:\\Windows\\file.txt', 'C:\\');
+--------------------------------------------+------------------------------------------------+
| STARTS_WITH('/home/user/file.txt', '/home') | STARTS_WITH('C:\\Windows\\file.txt', 'C:\\') |
+--------------------------------------------+------------------------------------------------+
| 1 | 1 |
+--------------------------------------------+------------------------------------------------+
- UTF-8 multi-byte characters
SELECT STARTS_WITH('ṭṛì ḍḍumai hello', 'ṭṛì'), STARTS_WITH('ṭṛì ḍḍumai hello', 'ḍḍumai');
+------------------------------------------+---------------------------------------------+
| STARTS_WITH('ṭṛì ḍḍumai hello', 'ṭṛì') | STARTS_WITH('ṭṛì ḍḍumai hello', 'ḍḍumai') |
+------------------------------------------+---------------------------------------------+
| 1 | 0 |
+------------------------------------------+---------------------------------------------+
- URL and protocol checking
SELECT STARTS_WITH('https://example.com', 'https://'), STARTS_WITH('ftp://server.com', 'http://');
+----------------------------------------------+---------------------------------------------+
| STARTS_WITH('https://example.com', 'https://') | STARTS_WITH('ftp://server.com', 'http://') |
+----------------------------------------------+---------------------------------------------+
| 1 | 0 |
+----------------------------------------------+---------------------------------------------+
- Numeric string prefix
SELECT STARTS_WITH('123456789', '123'), STARTS_WITH('987654321', '123');
+----------------------------------+----------------------------------+
| STARTS_WITH('123456789', '123') | STARTS_WITH('987654321', '123') |
+----------------------------------+----------------------------------+
| 1 | 0 |
+----------------------------------+----------------------------------+
- Special characters and symbols
SELECT STARTS_WITH('@username', '@'), STARTS_WITH('#hashtag', '#');
+-------------------------------+--------------------------------+
| STARTS_WITH('@username', '@') | STARTS_WITH('#hashtag', '#') |
+-------------------------------+--------------------------------+
| 1 | 1 |
+-------------------------------+--------------------------------+
Keywords
STARTS_WITH
