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.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.