What is SUBSTRING Function in SQL? [ Explained with Examples]

Have you ever come across the need to extract a specific portion of a string in SQL? You can retrieve the first few characters of a column or isolate a substring based on a specific position or length. In such cases, the SUBSTRING SQL comes to the rescue. You may quickly alter strings with this effective tool, giving your database queries more flexibility. This comprehensive tutorial will cover all aspects of SUBSTRING SQL and provide a thorough overview of its syntax, application, performance advice, and practical examples.

What is a SUBSTRING Function in SQL?

The SUBSTRING function is a powerful tool in SQL that allows us to extract a portion of a string based on specified criteria. It allows us to efficiently manipulate and analyze textual data, whether we need to extract a specific number of characters, retrieve a substring from a particular position, or extract a substring based on a pattern. In this article, we will explore the various applications and syntax of the SUBSTRING function in SQL.

Syntax of Substring in SQL

The syntax of the SUBSTRING function in SQL varies slightly depending on which DBMS you are using However, the general syntax is as follows:

For most DBMS

SUBSTRING(string_expression, start_position, length)

For some DBMS (e.g., Oracle)

SUBSTR(string_expression, start_position, length)

• string_expression: The input string from which the substring will be extracted.
• start_position: The starting position of the substring within the input string. It is typically an integer value.
• length: (Optional) The length of the substring to be extracted. If omitted, the substring will be extracted from the start_position to the end of the input string.

It’s important to note that the start_position parameter is usually 1-based, meaning the first character of the input string is at position 1.

Also Read: Top 10 SQL Projects for Beginners, Intermediate and Experienced Learners

SUBSTRING SQL Queries and Examples

To further solidify your understanding of SUBSTRING in SQL, let’s delve into practical examples of SUBSTRING queries that demonstrate its versatility and application in various scenarios. These examples will cover a wide range of use cases, ranging from simple extractions to more advanced queries.

Example 1: Extracting First Names

Suppose you have a column called full_name in a table that stores the full names of individuals. You want to extract only the first names from this column. You can achieve this using Substring as follows:

SELECT SUBSTRING(full_name, 1, CHARINDEX(' ', full_name) - 1) AS first_name
FROM table_name;

In this example, the Substring function is combined with CharIndex to locate the position of the first space character, which separates the first name from the last name. The Substring function then extracts the substring from the beginning of the full_name column until the position of the space character minus one.

Example 2: Trimming URLs

Suppose you have a column called url that contains URLs with unnecessary characters at the beginning and end. You want to extract the core part of the URL by removing the protocol (e.g., “http://” or “https://”) and any trailing slashes. You can accomplish this with Substring as follows:

SELECT SUBSTRING(url, CHARINDEX('//', url) + 2, LEN(url) - CHARINDEX('//', url) - 1) AS trimmed_url
FROM table_name;

In this example, the Substring function is used to extract the substring starting from the position after the “//” characters until the end of the URL. By subtracting the position of the “//” characters from the length of the URL, we ensure that any trailing slashes are excluded.

Example 3: Extracting Phone Number Area Codes

Suppose you have a column called phone_number that contains phone numbers in the format “(XXX) XXX-XXXX”, where XXX represents the area code. You want to extract the area codes from these phone numbers. You can achieve this using Substring as follows:

SELECT SUBSTRING(phone_number, 2, 3) AS area_code
FROM table_name;

In this example, the Substring function is utilized to extract the substring starting from the second character (omitting the opening parenthesis) with a length of three characters. This effectively captures the area code portion of the phone numbers.

Explore advanced SQL topics here.

Difference between Substring and CharIndex

Here’s a table comparing the SUBSTRING and CHARINDEX functions in SQL:

Using these functions together, you can perform various string manipulation tasks in SQL, such as extracting substrings and finding their positions within strings.

SQL Full Course

Conclusion

The Substring function in SQL provides a powerful tool for manipulating strings, extracting specific portions, and performing data transformations. By mastering the Substring syntax, understanding its usage, and following performance optimization techniques, you can unlock the full potential of this function in your SQL queries. Armed with the knowledge gained from this ultimate guide, you can confidently tackle string manipulations and extract valuable insights from your database.

Frequently Asked Questions