SQL Essentials Training & Certification
- 5k Enrolled Learners
- Self Paced
Structured Query Language aims to give users the ability to retrieve the data in a format they want. In this article on a substring in SQL, I will show you how to extract a set of characters from a string. The following topics will be covered in this article:
Let us get started!
SQL or Structured Query Language is developed by Donald D.Chamberlin and is used to manage, access and retrieve data in a database. It consists of various commands segregated into 4 categories (DDL, DML, DCL, and TCL). SUBSTRING is one such command in SQL, used to retrieve a set of characters from the specified string.
SUBSTRING in SQL is a function used to retrieve characters from a string. With the help of this function, you can retrieve any number of substrings from a single string.
SUBSTRING(string, starting_value, length)
Refer the image below for the pictorial representation of SUBSTRING in SQL.
Since you have understood the syntax and the rules to use the SUBSTRING in SQL, let us now discuss the various ways to use it.
For your better understanding, I have divided the examples into following sections:
Let us look into each one of them.
When you use SUBSTRING in SQL for literals, it extracts a substring from the specified string with a length and the starting from the initial value mentioned by the user.
Write a query to extract a substring from the string “Edureka”, starting from the 2nd character and must contain 4 characters.
SELECT SUBSTRING(‘Edureka’, 2, 4);
Write a query to extract a substring of 8 characters, starting from the 2nd character from the string “Edureka”. Here, if you observe, we need to extract a substring with the length greater than the maximum length of the expression.
SELECT SUBSTRING(‘Edureka’, 2, 8);
Consider the below table with the table name Customers.
Write a query to extract a substring of 3 characters, starting for the 1st character for the CustName “Akash”.
SELECT SUBSTRING(CustName, 1, 3) FROM Customers WHERE CustName = ‘Akash’;
Write a query to extract a substring till the end of the string, starting for the 2nd character from the CustName “Akash”.
SELECT SUBSTRING(CustName, 2) FROM Customers WHERE CustName = ‘Akash’;
Write a query to extract a substring of 3 characters, starting for the 2nd character for the CustName and order it according to the CustName.
SELECT CustName FROM Customers ORDER BY SUBSTRING(CustName, 2, 3);
anj ita kas nuj ona
In this section of this article on a substring in SQL, let us understand how to use the SUBSTRING function on nested queries. To understand the same, let us consider the Customers table, we have considered above.
Write a query to extract all the domain from the CustEmail column on the Customers table.
SELECT CustEmail, SUBSTRING( CustEmail, CHARINDEX('@', CustEmail)+1, LEN(CustEmail)-CHARINDEX('@', CustEmail) ) Domain FROM Customers ORDER BY CustEmail;
Since the domain starts after the @ character, we have used the CHARINDEX() function to search for the @character in the CustEmail column. Then the result of this function is used to determine the starting position and the length of the substring to be extracted.
So, folks that’s how, you can use the SUBSTRING function in SQL, to retrieve data. With that, we come to an end of this article on SUBSTRING in SQL. I hope you found this article informative.
If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of this article and I will get back to you.