MySQL in a Nutshell, 2nd Edition

Name

INSERT()

Synopsis

INSERT(string, position, length, new_string)

This function inserts the string from the final argument into the string specified by the first argument, at the specified position. If length is greater than 0, the function overwrites that number of characters, so the new string replaces part of the original. The function returns NULL if any of the arguments are NULL. The first position is 1. Don’t confuse this function with the SQL INSERT statement. Here is an example of this function:

UPDATE courses
SET course_name =
INSERT(course_name, INSTR(course_name, 'Eng.'), 4, 'English')
WHERE course_name LIKE "%Eng.%";

In this example, some course names have the word English abbreviated as Eng. This SQL statement overwrites any such occurrences with the word English. It uses the INSTR() function to find the starting point of the abbreviation. The number value it returns is used as the position argument for the INSERT() function. If it’s not found, the course name will not be changed because a value of 0 will be returned by INSTR(), and the INSERT() function ignores any request in which position lies outside the length of the original string.