INSERT( )

This MySQL function inserts the string from the final argument into the string specified by the first argument, at the specified position.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

INSERT(string, position, length, new_string)

Explanation

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 that 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.

Examples

UPDATE courses
SET course_name =
INSERT(course_name, INSTR(course_name, 'Eng.'), 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 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.