Introduction to MySQL Substring

MySQL Substring () function is used to extract the substring in the given string. We extract the substring from the given string by specifying the position of the string. The specification of the substring that needs to extracted by the arguments that we pass in the function.

MySQL Substring () function usually consists of three arguments first part is for the string, the second part is to specify the starting point from where you are willing to trim the data. The third part is the length of the substring which means the number of characters that we are trying to extract from the string.

In this session let us learn more about the Substring usage, syntax along with an example:

Syntax

Syntax of the substring () function is as below: –

substring(String_value, starting_position, length_of_characters);

String_value: It is to specify the actual string to extract from.

starting_position: It is a required argument. Here we specify the position of the string from where we are planning to extract. It can be a positive or negative value. Positive value extracts from the beginning. Negative extracts from the end of the string.

Length_of_characters: It can be optional. We can either specify the length of the substring that we are willing to extract or leave it. If we don’t specify the length whole string will be extracted from the “starting_position” value.

How does MySQL Substring () work?

Now let us see how the substring function works.

With just two arguments:

SELECT SUBSTRING('MySQL SUBSTRING',-10) AS OUTPUT;

Output:

SELECT SUBSTRING('MySQL SUBSTRING',10) AS OUTPUT;

Output:

SELECT SUBSTRING('MySQL SUBSTRING TEST FUNCTION',10) AS OUTPUT;

Output:

Three arguments:

Example:

SELECT SUBSTRING('MySQL SUBSTRING TEST FUNCTION',10,6) AS OUTPUT;

Output:

Example:

SELECT SUBSTRING('MySQL SUBSTRING TEST FUNCTION',-1,10)AS OUTPUT;

Output:

If we check the above example, we could see that we have used negative value in the position. Here it means that the last 1 string from the string is excluded.

Example:

SELECT SUBSTRING('MySQL SUBSTRING TEST FUNCTION',1,10) AS OUTPUT;

Output:

Examples of MySQL Substring

If we check the above example, we could see that we have used positive value in the position. Here it substring from the starting 1 position to 10.

Example #1

Let us create a table and apply the substring function on it: –

Create table test_substring ( Serial_number int, Company_name VARCHAR(50) );

Insert data into the table: –

insert into test_substring values (1,'corporation HP'); insert into test_substring values (2,'corporation Microsoft'); insert into test_substring values (3,'corporation General Motors'); insert into test_substring values (4,'corporation General electric'); insert into test_substring values (5,'corporation Granite'); insert into test_substring values (6,'corporation Fedex'); insert into test_substring values (7,'corporation Yahoo'); insert into test_substring values (8,'corporation Ikea');

Select the values from the above table: –

select * from test_substring;

Output:

Now let us extract on the company name from the above “Company_name” column. Which means exclude the string “corporation” in the entire column.

select *,SUBSTRING(Company_name , 13, 20)AS Organisation from test_substring;

Output:

select *,SUBSTRING(Company_name ,-7, 20)AS Organisation from test_substring;

Output:

Example #2

Let us create another table to extract the code from the table: –

create table code_values ( codeno int, code_message VARCHAR(5) );

Insert values into the tables: –

insert into code_values values (1,'Code: 45672 is generated for the error'); insert into code_values values (2,'Code: 23672 is generated for the error'); insert into code_values values (3,'Code: 46672 is generated for the error'); insert into code_values values (4,'Code: 76672 is generated for the error'); insert into code_values values (5,'Code: 98672 is generated for the error'); insert into code_values values (6,'Code: 12672 is generated for the error'); insert into code_values values (7,'Code: 76672 is generated for the error'); insert into code_values values (8,'Code: 34672 is generated for the error');