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:
Select SUBSTRING (‘encyclopedia’, 3, 5);
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.
Select SUBSTRING (‘encyclopedia’, 3, 14);
Using Substring on Conditional Tables
Consider the following table consisting of the customer id, customer name, and customer email id.
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.
Extract a substring of three characters, starting at the third character from the string “Jacob” from table 1, named Customers.
Select SUBSTRING(Customer_Name, 1, 3) from Customers where Customer_Name= ‘Jacob’;
Let’s move into another example, now.
Extract a substring of all characters, starting at the second character from the string “Jacob” from table 1, named Customers.
Select SUBSTRING(Customer_Name, 2) from Customers where Customer_Name= ‘Jacob’;
Finally, let’s look at a little advanced example.
Extract a substring of three characters, starting at the second character from all the strings of table 1, named Customers.
Select SUBSTRING(Customer_Name, 2, 3) from Customers order by SUBSTRING(Customer_Name, 2, 3);
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.
Select Customer_Email, SUBSTRING(Customer_Email, charindex (‘@’, Customer_Email) +1, \ len (Customer_Email) – charindex (‘@’, Customer_Email)) Domain From Customers Order by Customer_Email;
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.
- 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