The SUBSTRING_INDEX SQL Function in MySQL and MariaDB - Substring by delimiter |
|
| | SUBSTRING_INDEX | Syntax: | SUBSTRING_INDEX(String, Delimiter, Number) | | Return value: | CHAR | | Function type: | String function | |
| | The SUBSTRING_INDEX() function returns a substring of "String" before or after "Number" of occurrences of the delimiter "Delimiter".
If "Number" is positive, everything to the left of the last occurrence of the delimiter is returned. It is counted from the left.
If "Number" is negative, everything to the right of the last occurrence of the delimiter is returned. It is counted from the right.
If "Number" is 0 or "Delimiter" is an empty string, an empty string is returned.
If "Delimiter" was not found in the "String" or not the "Number" of "Delimiter", the function returns the complete "String".
If "String" or "Delimiter" contain numeric values, they are interpreted as character strings.
The SUBSTRING_INDEX() function is case sensitive when searching for delimiters.
If either argument is NULL, the SUBSTRING_INDEX() function returns NULL. | SQL Examples for the SUBSTRING_INDEX function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
| select substring_index('Test1-Test2', '-', 1);
select substring_index('Test1-Test2', 't', 1);
select substring_index('Test1-Test2', 't', 2);
select substring_index('Test1-Test2', '-', -1);
select substring_index('Test1-Test2', 't', -1);
select substring_index('Test1-Test2', 't', -2);
select substring_index(now(), ' ', 1);
select substring_index(now(), ' ', -1);
select substring_index(1234567, 4, 1);
select substring_index(1234567, 4, -1);
select substring_index('Test', 'x', 1);
select substring_index('Test', 't', 2);
select substring_index('Test', 't', -2);
select substring_index('Test', 't', 0);
select substring_index('Test', '', 1);
select substring_index(null, 't', 2);
select substring_index('Test-Test', null, 2);
select substring_index('Test-Test', 't', null);
|
|
| substring_index('Test1-Test2', '-', 1) |
|---|
| varchar(11) BINARY |
|---|
| Test1 |
|
|
| substring_index('Test1-Test2', 't', 1) |
|---|
| varchar(11) BINARY |
|---|
| Tes |
|
|
| substring_index('Test1-Test2', 't', 2) |
|---|
| varchar(11) BINARY |
|---|
| Test1-Tes |
|
|
| substring_index('Test1-Test2', '-', -1) |
|---|
| varchar(11) BINARY |
|---|
| Test2 |
|
|
| substring_index('Test1-Test2', 't', -1) |
|---|
| varchar(11) BINARY |
|---|
| 2 |
|
|
| substring_index('Test1-Test2', 't', -2) |
|---|
| varchar(11) BINARY |
|---|
| 1-Test2 |
|
|
| substring_index(now(), ' ', 1) |
|---|
| varchar(19) BINARY |
|---|
| 2023-03-23 |
|
|
| substring_index(now(), ' ', -1) |
|---|
| varchar(19) BINARY |
|---|
| 17:54:36 |
|
|
| substring_index(1234567, 4, 1) |
|---|
| varchar(7) BINARY |
|---|
| 123 |
|
|
| substring_index(1234567, 4, -1) |
|---|
| varchar(7) BINARY |
|---|
| 567 |
|
|
| substring_index('Test', 'x', 1) |
|---|
| varchar(4) BINARY |
|---|
| Test |
|
|
| substring_index('Test', 't', 2) |
|---|
| varchar(4) BINARY |
|---|
| Test |
|
|
| substring_index('Test', 't', -2) |
|---|
| varchar(4) BINARY |
|---|
| Test |
|
|
| substring_index('Test', 't', 0) |
|---|
| varchar(4) BINARY |
|---|
| |
|
|
| substring_index('Test', '', 1) |
|---|
| varchar(4) BINARY |
|---|
| |
|
|
| substring_index(null, 't', 2) |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| substring_index('Test-Test', null, 2) |
|---|
| varchar(9) BINARY |
|---|
| NULL |
|
|
| substring_index('Test-Test', 't', null) |
|---|
| varchar(9) BINARY |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the SUBSTRING_INDEX() function in MySQL and MariaDB databases | | In MySQL and MariaDB the function SUBSTRING_INDEX() can be used to extract a substring from a string based on a specified separator and a specified number of occurrences. It allows retrieving part of a string before or after a delimiter, based on the number of occurrences. SUBSTRING_INDEX() is commonly used to parse and manipulate strings that contain structured data such as URLs, file paths, or delimited values. | | Further MySQL und MariaDB SQL String functions | | CHAR | CHAR(Character code, [, Charset]) | | More about CHAR Function |
| | CHR | CHR(Character code) | | More about CHR Function |
| | CONCAT | CONCAT(Strings ('str1','str2', ...)) | | More about CONCAT Function |
| | CONCAT_WS | CONCAT_WS(Separator, Strings ('str1','str2', ...)) | | More about CONCAT_WS Function |
| | ELT | ELT(Index, Strings ('str1','str2', ...)) | | More about ELT Function |
| | EXPORT_SET | EXPORT_SET(Bits, On, Off [, Separator] [, Number of bits]) | | More about EXPORT_SET Function |
| | EXTRACTVALUE | EXTRACTVALUE(XML, XPath) |
| | FIELD | FIELD(Search string, Strings ('str1','str2', ...)) | | More about FIELD Function |
| | FORMAT | FORMAT(Number, Decimals [, Region]) | | More about FORMAT Function |
| | INSERT | INSERT(String, Position, Length, String to insert) | | More about INSERT Function |
| | LCASE | LCASE(String) | | More about LCASE Function |
| | LEFT | LEFT(String, Length) | | More about LEFT Function |
| | LOAD_FILE | LOAD_FILE(File name) |
| | LOWER | LOWER(String) | | More about LOWER Function |
| | LPAD | LPAD(String, Length, String to append) | | More about LPAD Function |
| | LPAD_ORACLE | LPAD_ORACLE(String, Length, String to append) | | More about LPAD_ORACLE Function |
| | LTRIM | LTRIM(String) | | More about LTRIM Function |
| | MAKE_SET | MAKE_SET(Bits, Strings ('str1','str2', ...)) | | More about MAKE_SET Function |
| | MID | MID(String, Position [, Length]) | | More about MID Function |
| | QUOTE | QUOTE(String) | | More about QUOTE Function |
| | REPEAT | REPEAT(String, Number) | | More about REPEAT Function |
| | REPLACE | REPLACE(String, Search, Replace) | | More about REPLACE Function |
| | REVERSE | REVERSE(String) | | More about REVERSE Function |
| | RIGHT | RIGHT(String, Length) | | More about RIGHT Function |
| | RPAD | RPAD(String, Length, String to append) | | More about RPAD Function |
| | RPAD_ORACLE | RPAD_ORACLE(String, Length, String to append) | | More about RPAD_ORACLE Function |
| | RTRIM | RTRIM(String) | | More about RTRIM Function |
| | SOUNDEX | SOUNDEX(String) | | More about SOUNDEX Function |
| | SPACE | SPACE(Number) | | More about SPACE Function |
| | SUBSTR | SUBSTR(String, Position, Length) | | More about SUBSTR Function |
| | SUBSTRING | SUBSTRING(String, Position, Length) | | More about SUBSTRING Function |
| | TRIM | TRIM([Type] [, Delete character], , String) | | More about TRIM Function |
| | TRIM_ORACLE | TRIM_ORACLE([Type] [, Delete character], , String) | | More about TRIM_ORACLE Function |
| | UCASE | UCASE(String) | | More about UCASE Function |
| | UPPER | UPPER(String) | | More about UPPER Function |
| | WEIGHT_STRING | WEIGHT_STRING(String [AS Format] [LEVEL Level] [Sorting]) | | More about WEIGHT_STRING Function |
| | ASCII | ASCII(String) | | More about ASCII Function |
| | BIT_LENGTH | BIT_LENGTH(String) | | More about BIT_LENGTH Function |
| | CHAR_LENGTH | CHAR_LENGTH(String) | | More about CHAR_LENGTH Function |
| | CHARACTER_LENGTH | CHARACTER_LENGTH(String) | | More about CHARACTER_LENGTH Function |
| | FIND_IN_SET | FIND_IN_SET(Search string, Set of values) | | More about FIND_IN_SET Function |
| | INSTR | INSTR(String, Search string) | | More about INSTR Function |
| | LENGTH | LENGTH(String) | | More about LENGTH Function |
| | LENGTHB | LENGTHB(String) | | More about LENGTHB Function |
| | LOCATE | LOCATE(Search string, String [, Position]) | | More about LOCATE Function |
| | OCTET_LENGTH | OCTET_LENGTH(String) | | More about OCTET_LENGTH Function |
| | ORD | ORD(String) | | More about ORD Function |
| | POSITION | POSITION(Search string IN String) | | More about POSITION Function |
|
|
| | | More information about the SUBSTRING_INDEX SQL function: and and |
|
|
|
|
