SPLIT_PART
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SPLIT_PART() function splits a string into substrings and retrieves the nth part, starting from 1.
SELECT split_part('a,b,c', ',', 2);
create.select(splitPart(val("a,b,c"), ",", 2)).fetch();
The result being
+------------+ | split_part | +------------+ | b | +------------+
Dialect support
This example using jOOQ:
splitPart(val("a,b,c"), ",", 2)
Translates to the following dialect specific expressions:
Aurora MySQL, MariaDB, MemSQL, MySQL
substring(
substring_index('a,b,c', ',', 2),
CASE 2
WHEN 1 THEN 1
ELSE (char_length(substring_index(
'a,b,c',
',',
(2 - 1)
)) + char_length(',') + 1)
END
)
Aurora Postgres, CockroachDB, Databricks, Postgres, Redshift, Snowflake, Trino, Vertica, YugabyteDB
split_part('a,b,c', ',', 2)
BigQuery
split('a,b,c', ',')[ORDINAL(2)]
DB2, Oracle
coalesce(
substr(
'a,b,c',
nullif(
decode(
2,
1,
1,
(nullif(
instr(
'a,b,c',
',',
1,
nullif(
(2 - 1),
0
)
),
0
) + length(','))
),
(length('a,b,c') + 1)
),
coalesce(
(nullif(
instr('a,b,c', ',', 1, 2),
0
) - decode(
2,
1,
1,
(nullif(
instr(
'a,b,c',
',',
1,
nullif(
(2 - 1),
0
)
),
0
) + length(','))
)),
((length('a,b,c') - nullif(
instr(
'a,b,c',
',',
1,
nullif(
(2 - 1),
0
)
),
0
)) - (length(',') - 1))
)
),
''
)
DuckDB
(str_split('a,b,c', ','))[2]
SQLServer
coalesce(
(
SELECT value
FROM string_split('a,b,c', ',', 1)
WHERE ordinal = 2
),
''
)
Teradata
strtok('a,b,c', ',', 2)
ASE, Access, ClickHouse, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Informix, SQLDataWarehouse, SQLite, Sybase
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!