Don't let the Lockdown slow you Down - Enroll Now and Get 3 Course at 24,999/- Only. Explore Now!

General

How to Retrieving Data Using Substring in SQL

How to Retrieving Data Using Substring in SQL

Introducing Substring in SQL

Substring is commonly defined as a function that is utilized to return a segment of a string. Different databases follow different means of doing so. For instance, in ORACLE, the function is SUBSTR(), in MYSQL, it is SUBSTR(), SUBSTRING(), and in the SQL server, it is merely SUBSTRING(). Now, one must understand that data could be retrieved in the desired format by using substrings. So, through this article, you will be apprised on how to return a group of characters from a whole string in SQL using the substring function.

Syntax of Substring in SQL

The syntax of a substring in SQL is as follows:

SUBSTRING(string, starting_value), length)

In the syntax, note that string denotes the string in the database table from which you require the extraction of a group of characters. The starting_value denotes the commencing position of a string. In any case, the initial character of the string is assigned a value of one. And finally, the length indicates the number of characters that must be obtained from the desired string.

The following are important details you need to keep in mind while using the substring function.

  • Indication of error is expected while the entered length parameter is given a negative value
  • It is permissible to enter the character length as one that is exceeding the actual and total length of the string itself. In such a case, you would retrieve the entire string, beginning at the commencing position mentioned in the string_value
  • It is mandatory to specify all the three fields represented in the substring syntax.
  • No value would be returned when the string_value is greater than the total number of characters in the string.

Examples of Substring in SQL

Let us now delve into the working functionalities of substrings in SQL. We would look at how substrings could be used in literals, conditional tables, and nested queries.

Using Substring on Literals

While utilizing the function SUBSTRING() in SQL, we obtain the substring from a particular string containing a predefined length, by beginning at the first value as mentioned by the user.

For instance, consider writing a query to retrieve to extract a substring, beginning at the third character of length five characters from the word encyclopedia.

The input and output are as follows:

Input

Select SUBSTRING (‘encyclopedia’, 3, 5);

Output

cyclo

Let us now consider another example where we need to retrieve a substring that is much greater than the maximum number of the total characters present in the string. Let’s assume that from the string encyclopedia, we need to extract a substring that is of fourteen characters, starting at the third character. Follow the input and output for the case in point.

Input

Select SUBSTRING (‘encyclopedia’, 3, 14);

Output

cyclopedia

Using Substring on Conditional Tables

Consider the following table consisting of the customer id, customer name, and customer email id.

Customer_IdCustomer_NameCustomer_Email
1Sarahsarah@gmail.com
2Merlinmerlin@gmail.com
3Jacobjacob@yahoo.com
4Edwardedward@bing.com
5Travistravis@gmail.com

Table 1: Customers

Assume that you’ve already created this table in your database. Let’s see how to implement the substring function in this conditional table now.

Query

Extract a substring of three characters, starting at the third character from the string “Jacob” from table 1, named Customers.

Input

Select SUBSTRING(Customer_Name, 1, 3)
from Customers
where Customer_Name= ‘Jacob’;

Output

jac

Let’s move into another example, now.

Query

Extract a substring of all characters, starting at the second character from the string “Jacob” from table 1, named Customers.

Input

Select SUBSTRING(Customer_Name, 2)
from Customers
where Customer_Name= ‘Jacob’;

Output

acob

Finally, let’s look at a little advanced example.

Query

Extract a substring of three characters, starting at the second character from all the strings of table 1, named Customers.

Input

Select SUBSTRING(Customer_Name, 2, 3)
from Customers
order by SUBSTRING(Customer_Name, 2, 3);

Output

ara
erl
aco
dwa
rav

Now, that we are clear with using the substring function in tables with conditions, let’s head over to using substrings in nested queries.

Using Substring in Nested Queries

For this section also, let us consider Table 1. Assume that you need to extract the domains from the available customer email ids.

Input

Select Customer_Email, SUBSTRING(Customer_Email, charindex (‘@’, Customer_Email) +1, \
len (Customer_Email) – charindex (‘@’, Customer_Email)) Domain
From Customers
Order by Customer_Email;

Output

Customer_EmailDomain
sarah@gmail.comgmail.com
merlin@gmail.comgmail.com
jacob@yahoo.comyahoo.com
edward@bing.combing.com
travis@gmail.comgmail.com

We used @ character since all the domains start after the said character. The charindex() function was used to look up for the @charcter in the email ids of the customers. Thus, through the result obtained from this function, we identified the starting position and also the length of the substrings that had to be retrieved.

Recap:

  • Substring – used to retrieve data of defined length and a commencing point from a predefined string
  • Syntax: SUBSTRING(string, starting_value), length)
  • Example 1: Using substring() in literals
  • Example 2: Using substring() on conditional tables
  • Example 3: Using substring() in nested queries

Related Blogs

  1. SQL Joins
  2. Schema in SQL
  3. Decode in SQL
  4. CASE Statement in MySQL
  5. Normalization in SQL
  6. SQL ORDER BY Clause
  7. LIKE Operator in SQL
  8. SQL Views
  9. SQL Concatenate Function
  10. What are the Manipulation Functions in SQL
  11. Primary Key In SQL
  12. SQL DateTime
  13. SQL Functions
Scroll Up
Besant Technologies WhatsApp