Bug #77346 Add string access operator []
Submitted: 14 Jun 2015 9:32 Modified: 15 Jun 2015 17:17
Reporter: Dima Soloviev Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[14 Jun 2015 9:32] Dima Soloviev
Description:
The idea is to make available to work with strings as character arrays (char arrays in c++...).
operator [index] and operator [index:len] in read querys (SELECT) works as a SUBSTRING(str,pos) and SUBSTRING(str,pos,len). The form without a len argument return a substring from string starting at position index. The forms with a len argument return a substring len characters long from string str, starting at position index.

operator [index]=val and operator [index:len]=val in write querys (UPDATE) works as INSERT(str,pos,len,newstr) or RPAD(str,len,padstr) + INSERT(str,pos,len,newstr).
1. String automatically right-padded with space for *char,text strings and NULL for *binary,blob strings to a length of val + index in form without a len argument, or to index+len in form with a len argument.
2. Substring beginning at position index and length of val characters long replaced by the val in form without a len argument. In form with a len argument substring beginning at position index and len characters long replaced by the val.
String elements numbering starting from 0.

How to repeat:
Examples:
-----------------------------------------------------------------------------
mysql> SELECT "abv123cdf"[4];
+----------------------------+
'23cdf'
-----------------------------------------------------------------------------
mysql> SELECT "abv123cdf"[4:2];
+----------------------------+
'23'
-----------------------------------------------------------------------------
mysql> SELECT "abv123cdf"[104:2];
+----------------------------+
''-----------------------------------------------------------------------------
mysql> CREATE TABLE `test` (
  `data` varchar(100) NOT NULL,
) ENGINE=InnoDB;
mysql> INSERT INTO `test` (`data`) VALUES('');
mysql> UPDATE `test` SET `data`[3] = 'abcmef';
mysql> SELECT * FROM `test`;
+----------------------------+
'   abcmef'
-----------------------------------------------------------------------------
mysql> CREATE TABLE `test` (
  `data` varchar(100) NOT NULL,
) ENGINE=InnoDB;
mysql> INSERT INTO `test` (`data`) VALUES('');
mysql> UPDATE `test` SET `data`[3:2] = 'abcmef';
mysql> SELECT * FROM `test`;
+----------------------------+
'   ab'
-----------------------------------------------------------------------------
mysql> CREATE TABLE `test` (
  `data` varchar(100) NOT NULL,
) ENGINE=InnoDB;
mysql> INSERT INTO `test` (`data`) VALUES('11111');
mysql> UPDATE `test` SET `data`[3:5] = 'abcmef';
mysql> SELECT * FROM `test`;
+----------------------------+
'111abcme'
-----------------------------------------------------------------------------
mysql> CREATE TABLE `test` (
  `data` varchar(100) NOT NULL,
) ENGINE=InnoDB;
mysql> INSERT INTO `test` (`data`) VALUES('11111');
mysql> UPDATE `test` SET `data`[1:1] = 'ab', `data`[3:1] = 'cd';
mysql> SELECT * FROM `test`;
+----------------------------+
'1a1c1'
-----------------------------------------------------------------------------