Name
POSITION()
Synopsis
POSITION(substring
INstring
)
This function returns an index of the character in
string
where
substring
first appears. The first
character of string
is numbered 1. This
function is like LOCATE()
, except that the
keyword IN
is used instead of a comma to separate
the substring and the containing string. Also, this function does not
provide a starting point to begin the search; it must begin from the
leftmost character. Here is an example:
UPDATE courses SET course_name = INSERT(course_name, POSITION('Eng.' IN course_name), 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 POSITION()
function to find the
starting point of the abbreviation. The numerical value it returns is
then used as the position argument for the
INSERT()
function (not the
INSERT
statement). If it’s not found, the course
name will not be changed, because a value of 0 will be returned by
POSITION()
, 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.