MySQL - MID() Function

The MySQL MID() function is used to extract a substring from a given string.

It accepts a string value, and a numerical value specifying a position (say pos) in the string, as parameters and returns the substring of the given string starting from the specified position pos to the end of the string.

It is similar to the MySQL SUBSTRING() function but differs in that it specifies the starting position and length of the substring, rather than specifying the ending position.

Syntax

Following is the syntax of MySQL MID() function −

MID(str, pos)

Parameters

This function takes a string value and a start position as parameter.

Return Value

This function returns the substring starting from the specified position in the given string.

Another Syntax

This function also has a second syntax. In this, in addition to the string and position values it also accepts another numerical value representing the desired length (say len) of the resultant sub string.

MID(str, pos, len)

Example

In the following example, we are extracting a substring starting from position 11 to the end of the string "Welcome to Tutorialspoint" −

SELECT MID('Welcome to Tutorialspoint', 11);

Following is the output of the above code −

MID('Welcome to Tutorialspoint', 11)
Tutorialspoint

Example

In here, we are retrieving a substring starting from position 15 and having a length of 15 −

SELECT MID('Hello how are you welcome to Tutorialspoint', 15, 15);

The output obtained is as follows −

MID('Hello how are you welcome to Tutorialspoint', 15, 15)
you welcome to

Example

You can also use the FROM clause in place of commas to specify pos and len, as shown in the following examples −

SELECT ('Welcome to Tutorialspoint' FROM 11);

We get the output as follows −

MID('Welcome to Tutorialspoint' FROM 11)
Tutorialspoint

Example

The MID() function also allows you to use negative values for pos. When you use a negative pos, the resulting substring starts after the specified number of characters from the end of the given string −

SELECT MID('Welcome to Tutorialspoint' FROM -11);

Following is the output of the above code −

MID('Welcome to Tutorialspoint' FROM -11)
orialspoint

Example

You can also pass numerical values in the form of strings as the first argument to this function −

SELECT MID('67326532 7822 879282 92', 15);

The result produced is as shown below −

MID('67326532 7822 879282 92', 15)
879282 92

Example

If any of the arguments passed to the function is NULL, the function returns NULL −

SELECT MID(NULL, 15);

We get the output as shown below −

MID(NULL, 15)
0x

Example

When you pass '0' as the pos parameter, the function returns an empty string −

SELECT MID('Hello how are you welcome to Tutorialspoint', 0, 15);

We get the output as follows −

MID('Hello how are you welcome to Tutorialspoint', 0, 15)

Example

If the argument passed as the value for the len parameter (second syntax) is less than 1, this function returns an empty string −

SELECT MID('Hello how are you welcome to Tutorialspoint', 5, -1);

Following is the output of the above code −

MID('Hello how are you welcome to Tutorialspoint', 5, -1)
orialspoint

Example

You can also get the substrings from the values of a column in a table using the MID() function.

Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE EMP(
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   INCOME FLOAT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP VALUES 
('Krishna', 'Sharma', 19, 2000),
('Raj', 'Kandukuri', 20, 7000),
('Ramya', 'Ramapriya', 25, 5000),
('Mac', 'Mohan', 26, 2000);

The EMP obtained is as follows −

FIRST_NAME LAST_NAME AGE INCOME
Krishna Sharma 19 2000
Raj Kandukuri 20 7000
Ramya Ramapriya 25 5000
Mac Mohan 26 2000

Following query retrieves substrings from the 'LAST_NAME' column using the MID() function −

SELECT FIRST_NAME, LAST_NAME, AGE, INCOME,  MID(LAST_NAME, 4) 
FROM EMP;

Output

After executing the above code, we get the following output −

FIRST_NAME LAST_NAME AGE INCOME MID(LAST_NAME, 4)
Krishna Sharma 19 2000 rma
Raj Kandukuri 20 7000 dukuri
Ramya Ramapriya 25 5000 apriya
Mac Mohan 26 2000 an

Example

In the following query, we are retrieving values in 'LAST_NAME' column from pos '2' with a length of '3' −

SELECT FIRST_NAME, LAST_NAME, AGE, INCOME, MID(LAST_NAME, 2, 3) 
FROM EMP;

Output

The result obtained is as follows −

FIRST_NAME LAST_NAME AGE INCOME MID(LAST_NAME, 2, 3)
Krishna Sharma 19 2000 har
Raj Kandukuri 20 7000 and
Ramya Ramapriya 25 5000 ama
Mac Mohan 26 2000 oha
mysql-mid-function.htm
Advertisements