MySQL in a Nutshell

Name

INSERT( )

Synopsis

INSERT(string, position, length, new_string)

This function inserts a string into another string at the specified position, optionally overwriting the new_string provided for a specified length. It returns NULL if any of the arguments are NULL. The first position is 1. To prevent overwriting, give a length of 0 for the third argument, which specifies how many characters to overwrite before inserting. Don’t confuse this function with the INSERT statement.

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

In this example, it was discovered that some course names have the word “English” abbreviated as “Eng.” This SQL statement overwrites any such occurrences with the word “English.” It uses the LOCATE( ) function to find the starting point of the abbreviation. This number is used as the position argument for the INSERT( ) function. If it’s not found, the course name will not be changed.