Updated May 17, 2023
Introduction to MySQL Substring
MySQL Substring () function extracts 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 be 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 we are trying to extract from the string.
In this session, let us learn more about Substring usage and 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 plan 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. The whole length string will be extracted from the “starting_position” value if we don’t specify.
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 can see that we have used a negative value in the position. Here it means that the last 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 can see that we have used a positive value in the position. Here it is substring from the starting one 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 the company name from the above “Company_name” column, excluding 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');Now let us select the data: –
select * from code_values;Output:
Now let us extract only the code value from the “code_message” column.
select *,SUBSTRING(code_message, 7,6)as code from code_values;Output:
Conclusion
MySQL Substring () function extracts the substring in the given string. We extract the substring from the given string by specifying the position of the string. The substring specification needs to be 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 we are trying to extract from the string.
Recommended Articles
We hope that this EDUCBA information on “MySQL Substring” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
