What happens if the position of insertion, in MySQL INSERT() function, is out of range?

MySQLMySQLi Database

MySQL INSERT() function performs no insertion if the position of insertion is out of range. The position of insertion can be out of range in the case when we pass a negative or 0(zero) value or the value goes beyond the value of a total number of characters in an original string by 2. It can be understood with the help of the following example −

Example

The query below will perform no insertion because the position of insertion is out of range i.e. a negative value.

mysql> Select INSERT('Virat', -1,5,'Kohli');
+-------------------------------+
| INSERT('Virat', -1,5,'Kohli') |
+-------------------------------+
| Virat                         |
+-------------------------------+
1 row in set (0.00 sec)

The query below will perform no insertion because the position of insertion is out of range i.e. 0 (zero).

mysql> Select INSERT('Virat', 0,5,'Kohli');
+------------------------------+
| INSERT('Virat', 0,5,'Kohli') |
+------------------------------+
| Virat                        |
+------------------------------+
1 row in set (0.00 sec)

The query below will perform no insertion because the position of insertion is out of range i.e. goes beyond the value of a number of characters in an original string by 2. In the example below, the original string ‘Virat’ is having 5 characters and the value of position we give is 7 hence no insertion happens.

mysql> Select INSERT('Virat', 7,5,'Kohli');
+------------------------------+
| INSERT('Virat', 7,5,'Kohli') |
+------------------------------+
| Virat                        |
+------------------------------+
1 row in set (0.00 sec)
raja
Published on 20-Feb-2018 08:19:45
Advertisements