EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Substring

MySQL Substring

Roja Metla
Article byRoja Metla
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 17, 2023

MySQL Substring

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL Substring output 1

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

Output:

MySQL Substring output 2

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

Output:

MySQL Substring output 3

Three arguments:

Example:

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

Output:

MySQL Substring output 4

Example:

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

Output:

MySQL Substring output 5

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:

MySQL Substring output 6

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:

output 7

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:

output 8

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

Output:

output 9

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:

output 10

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:

output 11

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.

  1. MySQL IN Operator
  2. Working on MySQL Subquery
  3. MySQL Constraints
  4. MySQL WHILE LOOP
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW