MID( )

This MySQL function returns the characters of a given string, starting from the position specified in the second argument.

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

MID(string, position[, length])

Explanation

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( ).

Examples

As an example of this function, suppose that a table of information on teachers contains a column listing their home telephone numbers. This column is in a format with all numbers, not 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 all our teachers live in the U.S., but 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.