Request: add support for postgresql specific scalar function: split_part(string text, delimiter text, field int)

[monetdb-team]

Date: 2014-09-05 15:50:24 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @hannesmuehleisen

Last updated: 2015-08-28 13:42:13 +0200

Comment 20139

Date: 2014-09-05 15:50:24 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

Although it is technically possible to rewrite some usages of split_part() using substring() and locate() functions it is not easy and will be less efficient than a native implementation.

Reproducible: Always

Steps to Reproduce:

select split_part('joeuser@mydatabase','@',0) AS "an error";
select split_part('joeuser@mydatabase','@',1) AS "joeuser";
select split_part('joeuser@mydatabase','@',2) AS "mydatabase";
select split_part('joeuser@mydatabase','@',3) AS "empty string";

select split_part('@joeuser@mydatabase@','@',2) AS "joeuser";

Actual Results:

Error: SELECT: no such operator 'split_part'
SQLState: 22000

An alternative would be to create a user defined function similar to http://www.sqlines.com/postgresql-to-oracle/split_part
but that would still not be as fast as a native c implementation.

Comment 20140

Date: 2014-09-05 15:53:15 +0200
From: Martin van Dinther <<martin.van.dinther>>

select split_part('joeuser@mydatabase','@',1) AS "joeuser";
-- MonetDB does not support split_part() but in some cases it can be replaced by using substring() and locate(), such as:
select substring('joeuser@mydatabase', 0, locate('@','joeuser@mydatabase', 0) -1) AS "joeuser";

select split_part('joeuser@mydatabase','@',2) AS "mydatabase";
-- MonetDB does not support split_part() but in some cases it can be replaced by using substring() and locate(), such as:
select substring('joeuser@mydatabase', locate('@','joeuser@mydatabase', 0) +1) AS "mydatabase";

select split_part('joeuser@mydatabase','@',3) AS "empty string";
-- MonetDB does not support split_part() but in some cases it can be replaced by using substring() and locate(), such as:
select substring('joeuser@mydatabase', locate('@','joeuser@mydatabase', 1) +1, locate('@','joeuser@mydatabase', 2) -1) AS "empty string";

select split_part('@joeuser@mydatabase@','@',2) AS "joeuser";
-- MonetDB does not support split_part() but in some cases it can be replaced by using substring() and locate(), such as:
select substring('@joeuser@mydatabase@', locate('@','@joeuser@mydatabase@', 0) +1, locate('@','@joeuser@mydatabase@', 1) -1) AS "joeuser";

Comment 20141

Date: 2014-09-05 15:58:13 +0200
From: MonetDB Mercurial Repository <>

Changeset 6a59cf45bf42 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=6a59cf45bf42

Changeset description:

Added MonetDB equivalent queries for replacing usage of split_part()
See also bug #3564

Comment 20196

Date: 2014-09-24 02:46:32 +0200
From: @skinkie

Created attachment 297
Implementation for split_part for MonetDB5/SQL

While searching for the same problem this evening, and almost posting to the MonetDB5 user mailinglist, I noticed that in December 17 2013 someone attempted to implement a varchar split. I was fooling around with:

select substring('HTM:1:1000', 0, POSITION(':' IN 'HTM:1:100') - 1), left(substring('HTM:1:1000', POSITION(':' IN 'HTM:1:100') + 1), POSITION(':' IN substring('HTM:1:1000', POSITION(':' IN 'HTM:1:100') + 1))-1), substring(substring('HTM:1:1000', POSITION(':' IN 'HTM:1:100') + 1), POSITION(':' IN substring('HTM:1:1000', POSITION(':' IN 'HTM:1:100') + 1))+1);

Better stop this unreadible madness and implement the most common used function for this problem: split_part.

Attached file: splitpart-monetdb.sql (text/plain, 5024 bytes)
Description: Implementation for split_part for MonetDB5/SQL

Comment 20197

Date: 2014-09-24 02:48:49 +0200
From: @skinkie

The output of Martin's queries:

sql>select split_part('joeuser@mydatabase','@',1) AS "joeuser";
+--------------------+
| joeuser |
+====================+
| joeuser |
+--------------------+
1 tuple (0.549ms)
sql>select split_part('joeuser@mydatabase','@',2) AS "mydatabase";
+--------------------+
| mydatabase |
+====================+
| mydatabase |
+--------------------+
1 tuple (0.519ms)
sql>select split_part('joeuser@mydatabase','@',3) AS "empty string";
+--------------------+
| empty string |
+====================+
| null |
+--------------------+
1 tuple (0.590ms)
sql>
sql>select split_part('@joeuser@mydatabase@','@',2) AS "joeuser";
+--------------------+
| joeuser |
+====================+
| joeuser |
+--------------------+
1 tuple (0.241ms)

Comment 20316

Date: 2014-10-25 18:24:48 +0200
From: @skinkie

Created attachment 304
Implementation for split_part for MonetDB5/SQL v2

Now including "PostgreSQL empty string compatibility" and syncs up with Oct2014.

select split_part('joeuser@mydatabase','@',0) AS "an error";
field position must be greater than zero

select split_part('joeuser@mydatabase','@',1) AS "joeuser";
+--------------------+
| joeuser |
+====================+
| joeuser |
+--------------------+
1 tuple (1.618ms)

select split_part('joeuser@mydatabase','@',2) AS "mydatabase";
+--------------------+
| mydatabase |
+====================+
| mydatabase |
+--------------------+
1 tuple (1.171ms)

sql>select split_part('joeuser@mydatabase','@',3) AS "empty string";
+--------------------+
| empty string |
+====================+
| |
+--------------------+
1 tuple (1.327ms)

Attached file: patch (text/plain, 5443 bytes)
Description: Implementation for split_part for MonetDB5/SQL v2

Comment 20894

Date: 2015-05-27 11:07:50 +0200
From: @skinkie

Created attachment 334
Implementation for split_part for MonetDB5/SQL v2

Update to apply cleanly.

Attached file: split_part.diff (text/plain, 5517 bytes)
Description: Implementation for split_part for MonetDB5/SQL v2

Comment 20906

Date: 2015-06-05 15:00:09 +0200
From: @skinkie

Created attachment 336
Patch for the MonetDB default branch

Attached file: latest-default.diff (application/octet-stream, 5520 bytes)
Description: Patch for the MonetDB default branch

Comment 20910

Date: 2015-06-08 15:19:41 +0200
From: MonetDB Mercurial Repository <>

Changeset 92e8a2746ce6 made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=92e8a2746ce6

Changeset description:

string_split('a|b','|',1), Bug #3564, Patch from Stefan de Konink, Test case added

Comment 20920

Date: 2015-06-11 16:22:33 +0200
From: MonetDB Mercurial Repository <>

Changeset a25f3dc6cc69 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=a25f3dc6cc69

Changeset description:

scalar function split_part() has been implemented, see request bug #3564.
Removed no longer needed MonetDB alternatives from strings.sql
Approve changed test outputs for strings.sql

Comment 21207

Date: 2015-08-28 13:42:13 +0200
From: @sjoerdmullender

Jul2015 has been released.