New versions: Dev (3.16) | Latest (3.15)

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, VERTICA
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)))), '')

-- ACCESS, ASE, DERBY, EXASOL, FIREBIRD, H2, HANA, HSQLDB, IGNITE, INFORMIX, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, 
-- TERADATA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.16, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo