You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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";
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
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
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:
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.
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)
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.
Comment 20906
Date: 2015-06-05 15:00:09 +0200
From: @skinkie
Created attachment 336
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:
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:
Comment 21207
Date: 2015-08-28 13:42:13 +0200
From: @sjoerdmullender
Jul2015 has been released.
The text was updated successfully, but these errors were encountered: