Bug #69879 FR: split() function for strings

• Submitted: 31 Jul 2013 14:41
• Reporter: Leandro Morgado
• Status: Verified
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• Version: 5.6+
• OS: Any
• CPU Architecture: Any

[31 Jul 2013 14:41] Leandro Morgado

Description:
This is a feature request for a string split function similar to what we have in languages like Perl. 

Here is an example of how this could work:
=======
SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

+-------+
| third |
+-------+
| ccc   |
+-------+
========

Where:

'a|bb|ccc|dd' - string to be split
'|' - delimiter 
3 - position to return

Currently this is can be done with nested SUBSTRING_INDEX() as follows:
========
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a|bb|ccc|dd', '|', 3), '|', -1 ) as third;
+-------+
| third |
+-------+
| ccc   |
+-------+
1 row in set (0.00 sec)
========

While this works fine, it makes SQL code much harder to read. With nested SUBSTRING_INDEX() it's not immediately obvious that we are splitting a string. 

How to repeat:
N/A

Suggested fix:
Create a SPLIT_STR (or whatever you wish to name it) function that is functionally equivalent to the two nested SUBSTRING_INDEX()s example above.