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)
ClickHouse
substringIndex('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'
)
ASE, Access, Aurora Postgres, BigQuery, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!