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_Id	Customer_Name	Customer_Email
1	Sarah	sarah@gmail.com
2	Merlin	merlin@gmail.com
3	Jacob	jacob@yahoo.com
4	Edward	edward@bing.com
5	Travis	travis@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_Email	Domain
sarah@gmail.com	gmail.com
merlin@gmail.com	gmail.com
jacob@yahoo.com	yahoo.com
edward@bing.com	bing.com
travis@gmail.com	gmail.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