SUBSTRING_INDEX
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SUBSTRING_INDEX() function gets a substring of a string, from the beginning until the nth occurrence of a delimiter.
SELECT
substring_index('a,b,c,d', ',', 2),
substring_index('a,b,c,d', ',', 3);
create.select(
substringIndex(val("a,b,c,d"), ",", 2),
substringIndex(val("a,b,c,d"), ",", 3)).fetch();
The result being
+-----------------+-----------------+ | substring_index | substring_index | +-----------------+-----------------+ | a,b | a,b,c | +-----------------+-----------------+
Dialect support
This example using jOOQ:
substringIndex(val("a,b,c,d"), ",", 3)
Translates to the following dialect specific expressions:
-- AURORA_MYSQL, MARIADB, MEMSQL, MYSQL
substring_index('a,b,c,d', ',', 3)
-- DB2, ORACLE
coalesce(
substr(
'a,b,c,d',
1,
(nullif(
instr('a,b,c,d', ',', 1, 3),
0
) - 1)
),
'a,b,c,d'
)
-- VERTICA
coalesce(
substring(
'a,b,c,d',
1,
(nullif(
instr('a,b,c,d', ',', 1, 3),
0
) - 1)
),
'a,b,c,d'
)
-- ACCESS, ASE, AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DERBY, DUCKDB, EXASOL, FIREBIRD, H2, HANA, HSQLDB, INFORMIX,
-- POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, YUGABYTEDB
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!