MySQL SUBSTR() Function

---

---

The MySQL SUBSTR() function is used to extract a substring from a given string. It takes three arguments: the original string, the position at which to start extraction, and the length of the substring to be extracted.

This function is useful in situations where you only need a specific part of a string, such as when working with names, addresses, or other types of text data.

The SUBSTR() function is equivalent to the MySQL SUBSTRING() function.

Syntax

Following is the syntax of MySQL SUBSTR() function −

SUBSTR(str,pos)
or,
SUBSTR(str FROM pos);

Parameters

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

Return Value

This function returns a 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.

SUBSTR(str,pos,len)
or,
SUBSTRING(str FROM pos FOR len)

Example

In the following example, we are extracting a substring starting from position 11 −

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

Following is the output of the above code −

SUBSTR('Welcome to Tutorialspoint', 11)
Tutorialspoint

Example

Following example demonstrates the usage of the second syntax of the SUBSTR() function. In here, we are trying to get the substring of the string "Hello how are you welcome to Tutorialspoint" starting from the position "15" and the length of the result is "15" −

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

The output obtained is as follows −

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

Example

You can also use the FROM clause in between the two arguments, instead of comma ',' −

SELECT SUBSTR('Welcome to Tutorialspoint' FROM 11);

The result produced is as follows −

SUBSTR('Welcome to Tutorialspoint' FROM 11)
Tutorialspoint

Example

You can also pass a negative value as the position. If you do so, the resultant substring will start after the specified number of characters from the end of the given string −

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

Following is the output of the above code −

SUBSTR('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 SUBSTR('67326532 7822 879282 92', 15);

Output obtained is as follows −

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

Example

If any of the given arguments is NULL, the function returns NULL −

SELECT SUBSTR(NULL, 15);

The result produced is as shown below −

SUBSTR(NULL, 15)
0x

Example

If the argument passed as the value for the pos parameter is 0, the function returns an empty string −

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

Following is the output of the above code −

SUBSTR('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, the function returns an empty string −

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

The result obtained is as follows −

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

Example

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 returns the substring of the entities of the column 'LAST_NAME' from the EMP table −

SELECT FIRST_NAME, LAST_NAME, AGE, SUBSTR(LAST_NAME, 4) as result
FROM EMP;

Output

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

FIRST_NAME	LAST_NAME	AGE	result
Krishna	Sharma	19	rma
Raj	Kandukuri	20	dukuri
Ramya	Ramapriya	25	apriya
Mac	Mohan	26	an

Example

The following query returns the substring of the 'LAST_NAME' column starting from the second character and including the next five characters −

SELECT FIRST_NAME, LAST_NAME, AGE, SUBSTR(LAST_NAME, 2, 5) as result
FROM EMP;

Output

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

FIRST_NAME	LAST_NAME	AGE	result
Krishna	Sharma	19	harma
Raj	Kandukuri	20	anduk
Ramya	Ramapriya	25	amapr
Mac	Mohan	26	ohan