ENDS_WITH
Description
The ENDS_WITH function checks whether a string ends with the specified suffix.
Syntax
ENDS_WITH(<str>, <suffix>)
Parameters
| Parameter | Description |
|---|---|
str | The main string to check. Type: VARCHAR |
suffix | The suffix string to match. Type: VARCHAR |
Return Value
Returns BOOLEAN type (displayed as TINYINT in Doris, where 1 represents true and 0 represents false).
Matching rules:
- Exact suffix match, case-sensitive
- Empty suffix matches any string (returns true)
- Supports correct matching of UTF-8 multi-byte characters
- Suffix length cannot exceed main string length (unless suffix is empty)
Special cases:
- If either parameter is NULL, returns NULL
- If suffix is an empty string, returns true (any string ends with an empty string)
- If main string is empty but suffix is not, returns false
- If both are empty strings, returns true
Examples
- Basic suffix matching
SELECT ENDS_WITH('Hello doris', 'doris'), ENDS_WITH('Hello doris', 'Hello');
+-----------------------------------+-----------------------------------+
| ENDS_WITH('Hello doris', 'doris') | ENDS_WITH('Hello doris', 'Hello') |
+-----------------------------------+-----------------------------------+
| 1 | 0 |
+-----------------------------------+-----------------------------------+
- Case sensitivity
SELECT ENDS_WITH('Hello World', 'world'), ENDS_WITH('Hello World', 'World');
+-----------------------------------+-----------------------------------+
| ENDS_WITH('Hello World', 'world') | ENDS_WITH('Hello World', 'World') |
+-----------------------------------+-----------------------------------+
| 0 | 1 |
+-----------------------------------+-----------------------------------+
- NULL value handling
SELECT ENDS_WITH(NULL, 'test'), ENDS_WITH('test', NULL);
+--------------------------+--------------------------+
| ENDS_WITH(NULL, 'test') | ENDS_WITH('test', NULL) |
+--------------------------+--------------------------+
| NULL | NULL |
+--------------------------+--------------------------+
- Empty string handling
SELECT ENDS_WITH('hello', ''), ENDS_WITH('', 'world');
+-------------------------+--------------------------+
| ENDS_WITH('hello', '') | ENDS_WITH('', 'world') |
+-------------------------+--------------------------+
| 1 | 0 |
+-------------------------+--------------------------+
- Complete string matching
SELECT ENDS_WITH('test', 'test'), ENDS_WITH('testing', 'test');
+---------------------------+------------------------------+
| ENDS_WITH('test', 'test') | ENDS_WITH('testing', 'test') |
+---------------------------+------------------------------+
| 1 | 1 |
+---------------------------+------------------------------+
- File extension check
SELECT ENDS_WITH('document.pdf', '.pdf'), ENDS_WITH('image.jpg', '.png');
+------------------------------------+----------------------------------+
| ENDS_WITH('document.pdf', '.pdf') | ENDS_WITH('image.jpg', '.png') |
+------------------------------------+----------------------------------+
| 1 | 0 |
+------------------------------------+----------------------------------+
- UTF-8 multi-byte characters
SELECT ENDS_WITH('hello ṭṛì ḍḍumai', 'ḍḍumai'), ENDS_WITH('hello ṭṛì ḍḍumai', 'ṭṛì');
+------------------------------------------+---------------------------------------+
| ENDS_WITH('hello ṭṛì ḍḍumai', 'ḍḍumai') | ENDS_WITH('hello ṭṛì ḍḍumai', 'ṭṛì') |
+------------------------------------------+---------------------------------------+
| 1 | 0 |
+------------------------------------------+---------------------------------------+
- URL path check
SELECT ENDS_WITH('https://example.com/api', '/api'), ENDS_WITH('https://example.com/', '.html');
+--------------------------------------------+---------------------------------------------+
| ENDS_WITH('https://example.com/api', '/api') | ENDS_WITH('https://example.com/', '.html') |
+--------------------------------------------+---------------------------------------------+
| 1 | 0 |
+--------------------------------------------+---------------------------------------------+
- Numeric string suffix
SELECT ENDS_WITH('123456789', '789'), ENDS_WITH('123456789', '456');
+--------------------------------+--------------------------------+
| ENDS_WITH('123456789', '789') | ENDS_WITH('123456789', '456') |
+--------------------------------+--------------------------------+
| 1 | 0 |
+--------------------------------+--------------------------------+
- Email domain check
SELECT ENDS_WITH('user@gmail.com', '.com'), ENDS_WITH('admin@company.org', '.com');
+------------------------------------+--------------------------------------+
| ENDS_WITH('user@gmail.com', '.com') | ENDS_WITH('admin@company.org', '.com') |
+------------------------------------+--------------------------------------+
| 1 | 0 |
+------------------------------------+--------------------------------------+
