Name
MID()
Synopsis
MID(string
,position
[,length
])
This function returns the characters of a given string,
starting from the position specified in the second argument. The first
character is numbered 1. You can limit the length of the string
retrieved by specifying a limit in the third argument. This function
is similar to SUBSTRING()
.
As an example of this function, suppose that a table of
information about teachers contains a column listing their home
telephone numbers. This column’s entries are in a format showing only
numbers, no hyphens or other separators (e.g.,
50412345678
). Suppose further that we decide to add
the country code and hyphens in a typical U.S. format (e.g.,
+1-504-123-45678
) because although all our teachers
live in the U.S., we’re about to acquire a small school in a different
country. We could make these changes like so:
UPDATE teachers SET phone_home = CONCAT_WS('-', '+1', LEFT(phone_home, 3), MID(phone_home, 4, 3), MID(phone_home, 7) );
This convoluted SQL statement extracts each component of the
telephone number with the LEFT()
and
MID()
functions. Using
CONCAT_WS()
, the data is merged back together
along with the country code at the beginning. Components in the return
value are separated with a hyphen, which is given as its first
parameter.
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.