How to retrieve a set of characters using SUBSTRING in SQL?

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:

• What is SQL?
• What is SUBSTRING in SQL?
  1. Syntax:
• SUBSTRING Examples:
  1. Use SUBSTRING on Literals
  2. Use SUBSTRING on Table with conditions
  3. USE SUBSTRING on Nested Queries

Let us get started!

What is SQL? 

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.

Next, in this article let us delve deeper into what is SUBSTRING in SQL and how to use it.

What is SUBSTRING in SQL?

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.

Syntax:

SUBSTRING(string, starting_value, length)

Here,

• String – Represents the string from which you have to extract a set of characters.
• Starting_value – This represents the starting position of the string. The first character in the string is given the value 1.
• Length – Represents the number of characters which you wish to extract.

Refer the image below for the pictorial representation of SUBSTRING in SQL.

Note:  

• The SUBSTRING function will throw an error if the length parameter is negative.
• The length of the characters can exceed the maximum length of the original string. In such a scenario, the entire string will be extracted from the starting position mentioned.
• All the three fields are mandatory in this function
• If the starting position is greater than the maximum number of characters in the string, then nothing is returned.

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.

SUBSTRING Examples:

For your better understanding, I have divided the examples into following sections:

1. 1. Use SUBSTRING on Literals
   2. Use SUBSTRING on Table with conditions
   3. USE SUBSTRING on Nested Queries

Let us look into each one of them.

Use SUBSTRING on Literals

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.

Example 1

Write a query to extract a substring from the string “Edureka”, starting from the 2^nd character and must contain 4 characters.

 
SELECT SUBSTRING(‘Edureka’, 2, 4);

Output

dure

Example 2 

Write a query to extract a substring of 8 characters, starting from the 2^nd 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);

Output

dureka

Use SUBSTRING on Table with conditions

Consider the below table with the table name Customers.

If you wish to know how to create a a table and insert values in it, you can refer to the article on CREATE and INSERT statement.

Example 1

Write a query to extract a substring of 3 characters, starting for the 1^st character for the CustName “Akash”.

SELECT SUBSTRING(CustName, 1, 3)
FROM Customers
WHERE CustName = ‘Akash’;

Output

Aka

Example 2

Write a query to extract a substring till the end of the string, starting for the 2^nd character from the CustName “Akash”.

SELECT SUBSTRING(CustName, 2)
FROM Customers
WHERE CustName = ‘Akash’;

Output

kash

Example 3

Write a query to extract a substring of 3 characters, starting for the 2^nd character for the CustName and order it according to the CustName.

SELECT CustName
FROM Customers
ORDER BY SUBSTRING(CustName, 2, 3);

Output:

anj
ita
kas
nuj
ona

USE SUBSTRING on Nested Queries

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.

Example:

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;

Output:

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.