MySQL | INSERT method
Start your free 7-days trial now!
MySQL's INSERT(~) method inserts and replaces the specified number of characters len from a substring newstr at the specified position pos in the destination string str.
Parameters
1. str | string
The string where we will insert the substring.
2. pos | index
The starting position in str where the substring should be inserted.
3. len | number
The number of characters from the substring to insert and replace in the original string.
4. newstr | string
The substring to insert.
Return value
The string str, with the substring beginning at position pos and len characters long replaced by the string newstr.
Examples
Basic usage
To insert 2 characters from the substring 'Ph' at the start of 'Filming':
SELECT INSERT('Filming', 1, 2, 'Ph');
+----------------------------+| INSERT('Filming',1,2,'Ph') |+----------------------------+| Phlming |+----------------------------+
The 'Fi' in the original string is replaced by the new string 'Ph' which gives us return value 'Phlming'.
Pos parameter
The original string is returned if the provided pos is not within the length of the string:
SELECT INSERT('Filming', 9, 2, 'Ph');
+-------------------------------+| INSERT('Filming', 9, 2, 'Ph') |+-------------------------------+| Filming |+-------------------------------+
The start position of 9 is not within the length of 'Filming' hence the original string is returned.
Len parameter
The rest of the string is replaced if the provided len is not within the length of the string:
SELECT INSERT('Falafel', 5, 4, 'phil');
+---------------------------------+| INSERT('Falafel', 5, 4, 'phil') |+---------------------------------+| Falaphil |+---------------------------------+
We insert 4 characters 'phil' starting from position 5 in 'Falafel', despite the fact there are only three characters to replace ('fel') if we start from position 5.
Null argument
NULL is returned if any argument is NULL:
SELECT INSERT('Falafel', 5, 4, NULL);
+-------------------------------+| INSERT('Falafel', 5, 4, NULL) |+-------------------------------+| NULL |+-------------------------------+
