MySQL - SUBSTRING() Function

The MySQL SUBSTRING() 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 various scenarios, such as extracting a specific part of a name, address or any other text string.

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

Syntax

Following is the syntax of MySQL SUBSTRING() function −

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

Parameters

This function takes a string value and a start position as 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.

SUBSTRING(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 SUBSTRING('Welcome to Tutorialspoint', 11);

Following is the output of the above code −

SUBSTRING('Welcome to Tutorialspoint', 11)
Tutorialspoint

Example

Following example demonstrates the usage of the second syntax of the SUBSTRING() 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 SUBSTRING('Hello how are you welcome to Tutorialspoint', 15, 15);

The output obtained is as follows −

SUBSTRING('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 SUBSTRING('Welcome to Tutorialspoint' FROM 11);

The result produced is as follows −

SUBSTRING('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 SUBSTRING('Welcome to Tutorialspoint' FROM -11);

Following is the output of the above code −

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

Output obtained is as follows −

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

Example

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

SELECT SUBSTRING(NULL, 15);

The result produced is as shown below −

SUBSTRING(NULL, 15)
0x

Example

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

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

Following is the output of the above code −

SUBSTRING('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 SUBSTRING('Hello how are you welcome to Tutorialspoint', 5, -1);

The result obtained is as follows −

SUBSTRING('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, SUBSTRING(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, SUBSTRING(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
mysql-substring-function.htm
Advertisements