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, 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) -- ACCESS, ASE, DERBY, EXASOL, FIREBIRD, H2, HANA, HSQLDB, INFORMIX, SQLDATAWAREHOUSE, SQLITE, SYBASE /* 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!