SUBSTRING_INDEX
PostgreSQL equivalent:
Returns the part of a string before the given occurrence of a delimiter.
MySQL 8.1
SUBSTRING_INDEX(str, delimiter, count)Parameters
- strVARCHAR
- Source string
- delimiterVARCHAR
- Delimiter, case-sensitive
- countINT
- Occurrence number of the delimiter, a negative one counts from the end of the string
Return value
The part of the string to the left of the count-th delimiter, or with a negative count to the right of the count-th delimiter from the end; the whole string if there are fewer delimiters; an empty string when count is 0
Examples
Part before the second dot
MySQL 8.1
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);Part after the second dot from the end
MySQL 8.1
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);Domain from an email
MySQL 8.1
SELECT email,
SUBSTRING_INDEX(email, '@', -1) AS domain
FROM Users
LIMIT 5;Details
To take one part of a string by its number, like SPLIT_PART in PostgreSQL, nest two calls: first cut everything after the n-th delimiter, then take the last part. SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) returns mysql.