“`html

String adjustment is a frequent activity in SQL, with one of the most prevalently utilized functions for this goal being SUBSTRING. It aids in retrieving a specific segment of text from a more extensive string, making it particularly beneficial when purifying, formatting, or scrutinizing data. SQL SUBSTRING permits users to extract distinct characters from a string, empowering them to report on selected segments of the data efficiently. The function demands three primary parameters, which are the string to extract from, the initiation point of the extraction, and the count of characters to fetch. In this article, you’ll discover what the SQL SUBSTRING function entails and how it operates with various examples and their performance evaluations.

Index of Contents:

• What is SQL SUBSTRING?
• Syntax and Parameters of SQL SUBSTRING
• When to Utilize SQL SUBSTRING?
• How to Implement SQL SUBSTRING in Queries?
• Advanced Applications of SQL SUBSTRING
• SQL SUBSTRING Versus Other String Functions
• Frequent SQL SUBSTRING Errors and How to Prevent Them
• Recommended Practices for Employing SQL SUBSTRING
• Final Thoughts

What is SQL SUBSTRING?

SQL SUBSTRING is an integrated function utilized to retrieve a specific segment of a text string according to the starting position and a set length. This is primarily supported by relational databases such as MySQL, SQL Server, PostgreSQL, and Oracle, and is also applied in data manipulation. This function is vital in tasks related to data manipulation, cleansing, and transformation. By implementing SUBSTRING, you can adeptly extract relevant segments from larger text entries, simplifying the analysis and handling of textual information.

Syntax and Parameters of SQL SUBSTRING

The general structure of the SQL SUBSTRING() function:

SUBSTRING(string_expression, start_position, length)

Clarification:

• String_expression: The primary string from which the characters will be extracted.
• Start_position: The index to commence character extraction, based on a 1-based index (counting begins at 1, not 0).
• Length: The count of characters to retrieve beginning from the starting position.

Master SQL: Elevate Your Data Skills Today!

Harness the capabilities of databases with practical SQL training and become a data-driven expert

Discover Program

When to Utilize SQL SUBSTRING?

Employing SQL SUBSTRING() aids in optimizing data preparation and enhances the quality of data analysis, applicable in scenarios where:

1. You need to extract a segment of a string for data reporting.
2. You wish to clean or format raw text data.
3. You are parsing specific fields from concatenated strings.
4. You are dealing with unstructured text or logs.
5. You require meticulous control of text data for migration or ETL tasks.

1. Extracting a Segment of a String Using SQL SUBSTRING

Before extracting information, let’s first establish a dataset to illustrate the SQL SUBSTRING() function.

CREATE TABLE Company_name (
    ID INT,
    FullText VARCHAR(50),
    PhoneNumber VARCHAR(20)
);
-- Insert data into the table
INSERT INTO Company_name (ID, FullText, PhoneNumber)
VALUES
(1, 'ASKRUDRG Pvt Limited', '+1-543-1265486'),
(2, 'PGDRTVRSEH Pvt Limited', '+1-075-7578876');
SELECT * FROM Company_name;

Result:

This is the table post-creation, and now to extract a substring, you can leverage the SQL SUBSTRING() function to define the starting position and length of the desired substring.

Illustration:

SELECT 
    ID,
    SUBSTRING(FullText, 1, 6) AS ExtractedText
FROM 
   Company_name;

Result:

Clarification: In this case, the SUBSTRING() function extracted the initial 6 characters from the full text by limiting the string’s size.

2. Formatting or Purifying Data with the SQL SUBSTRING

The SUBSTRING function in SQL is frequently employed to format or cleanse entries within a table. It proves particularly advantageous when you wish to extract solely the pertinent part of a string while disregarding irrelevant details. In data warehousing and ETL initiatives, SUBSTRING() is typically utilized to isolate specific segments of a value, such as extracting a date, code, or identifier from a more extensive text entry. This function streamlines SQL string adjustments and enhances overall data integrity. Effectively using SUBSTRING() can lessen complexity and accelerate data processing.

Illustration:

SELECT 
    ID,
    SUBSTRING(PhoneNumber, 8, 6) AS LocalNumber
FROM 
    Company_name;

“““html

Clarification: In this illustration, the SQL SUBSTRING() function retrieved solely the local number while discarding the other digits from the dataset.

How to Implement SQL SUBSTRING in Queries?

The SQL SUBSTRING() function allows for the extraction of a specific segment of a string, dictated by the starting point and length. It is primarily utilized in queries for parsing, refining, and examining textual data.

Let’s commence by developing a sample dataset that will aid in demonstrating the functionality of the SQL SUBSTRING() function across various queries.

CREATE TABLE Employees (
    EmployeeID INT,
    FullName VARCHAR(20),
    DepartmentCode VARCHAR(10),
    PhoneNumber VARCHAR(20)
);
INSERT INTO Employees (EmployeeID, FullName, DepartmentCode, PhoneNumber)
VALUES
(1, 'Karan', 'HR-010', '+1-789-12343457'),
(2, 'Baskar', 'FIN-633', '+1-234-3254634'),
(3, 'Chowdary', 'MKT-459', '+1-877-346457'),
(4, 'Dhanush', 'SEC-452', '+1-443-354654776'),
(5, 'Madhan', 'FIN-234', '+1-344-45756786');
SELECT * FROM Employees;

Result:

This is a sample result displayed after constructing the table, showcasing the stored information.

1. Utilizing SQL SUBSTRING with String Constants

SUBSTRING() can be applied directly to a string to extract the specific segment of data from it.

Illustration:

SELECT SUBSTRING('Machine Learning', 9, 5) AS Extracted;

Result:

Clarification: This instance showcases how the SUBSTRING() function extracted the string starting from position 9 for a length of 5 characters.

2. Implementing SQL SUBSTRING with Column Data

The SUBSTRING() function can be employed on column data to extract elements such as names, codes, and numbers from the text contained within a table.

Illustration:

SELECT 
    FullName, 
    SUBSTRING(PhoneNumber, 9, 7) AS LocalNumber
FROM Employees;

Result:

Clarification: In this case, the SUBSTRING() function captured solely the local number from the employees’ complete phone numbers.

3. Implementing SQL SUBSTRING in a WHERE Clause

A SUBSTRING() can be utilized with a WHERE Clause to filter results based on specified conditions. This is particularly beneficial when dealing with structured data or codes.

Illustration:

SELECT *
FROM Employees
WHERE SUBSTRING(DepartmentCode, 1, 3) = 'FIN';

Result: 

Clarification: In this instance, the WHERE clause retrieved the details of employees whose department begins with “FINANCIAL.”

4. Incorporating SQL SUBSTRING in JOIN Conditions

The JOIN clause can be integrated with SUBSTRING() to associate two tables based on shared segments of their columns. The example below illustrates how to join the employees’ table to obtain the desired output.

Illustration:

CREATE TABLE Departments (
    DeptPrefix VARCHAR(3),
    DeptName VARCHAR(50)
);
INSERT INTO Departments (DeptPrefix, DeptName)
VALUES
('HR', 'Human Resources'),
('SEC', 'Secretary'),
('MKT', 'Marketing'),
('FIN', 'Finance');
SELECT 
    E.FullName, 
    D.DeptName
FROM Employees E
JOIN Departments D
    ON SUBSTRING(E.DepartmentCode, 1, 3) = D.DeptPrefix;

Result:

Clarification: This example illustrates how the JOIN clause linked two tables based on department information. The employee “Karan” is absent because the department prefix “HR” might not align due to data inconsistencies or formatting discrepancies in the JOIN condition.

Advanced Applications of SQL SUBSTRING

The SUBSTRING() function in SQL can also be utilized in sophisticated scenarios, including dynamic joins, conditional filtering, and targeted updates based on partial string matches.

1. Applying SQL SUBSTRING on Table Columns

The SQL SUBSTRING() extracts segments from a table column using specified start positions and lengths. It’s advantageous for structuring text such as IDs, dates, and codes within broader strings.

Illustration:

SELECT FullName, DepartmentCode, SUBSTRING(DepartmentCode, 1, 3) AS DeptPrefix FROM Employees;

Result:

Clarification: In this example, the SQL SUBSTRING() function pulls the name and department code starting from position 1, taking the initial 3 characters as a prefix.

2. SQL SUBSTRING for Modifying Column Values

SQL SUBSTRING can also be employed to update column values via the UPDATE statement to adjust the information contained within a table.

Illustration: If there is a need to modify or obfuscate an employee’s contact number, the UPDATE statement can be utilized.

“““html

UPDATE Employees
SET PhoneNumber = CONCAT(SUBSTRING(PhoneNumber, 1, 8), 'XXXXXXX')
WHERE EmployeeID = 1;
SELECT * FROM Employees;

Output:

Explanation: In this instance, the contact number of an employee, “Karan”, has been modified to “XXXXX” to obscure their contact information through an UPDATE statement utilizing the SUBSTRING() function.

SQL SUBSTRING vs Alternative String Functions

These are the distinctions between SQL SUBSTRING() and other String functions. However, refrain from using these functions within a WHERE or JOIN Clause when feasible to prevent impacting performance on large datasets.

Common SQL SUBSTRING Mistakes and How to Prevent Them

Some mistakes may arise when utilizing SQL SUBSTRING(). Below are frequent errors along with methods to prevent them.

1. Incorrect Use of Datatype

Error: This will trigger an error if the datatype comprises integers instead of strings.
To Prevent: Initially, convert the integer value to a VARCHAR type by employing CAST(column AS VARCHAR). This enables you to conduct string operations like SUBSTRING() on the altered data.

2. Invalid Starting Position or Length

Error: Should the start position specified in SUBSTRING() exceed the length of the string, SQL Server, MySQL, and PostgreSQL yield an empty string rather than an error, while Oracle issues an error.
To Prevent: ensure you use the LEN() function to verify the length prior to using SUBSTRING().

3. Handling NULL Incorrectly

Error: If you specify NULL in the text segment, the SUBSTRING will yield NULL.
To Prevent: Utilize the COALESCE function to retrieve a NULL value securely within the SUBSTRING().

4. Assuming SQL Operates on 0-Based Indexing

Error: SQL operates on a 1-based index, so ensure you do not presume zero-based indexing like in several programming languages.
To Prevent: Always ensure to state that the counting must commence from a value.

5. Unexpected Results in JOINs

Error: The issue will arise if the two joining tables do not produce matching outputs.
To Prevent: Check if both tables are compatible. If they are not, employ the TRIM() or CAST() function where applicable.

Best Practices for Employing SQL SUBSTRING

To effectively and efficiently leverage the SQL SUBSTRING() function in practice, here are several best practices to uphold:

1. Always Confirm Your Input: Utilize functions like LEN() or CHARINDEX() to ensure extraction will not result in an error.

2. Integrate the SUBSTRING() function with other SQL string functions: Implementing SUBSTRING() alongside dynamic content proves beneficial when used with CHARINDEX(), LEFT(), or RIGHT() collectively in the process.

3. Exercise caution in WHERE and JOIN statements: Avoid employing SUBSTRING() in WHERE or JOIN clauses on extensive tables as it hampers query performance.

4. Clearly Alias your output: Use clear aliases for your result columns, such as AS DeptPrefix, to enhance the understandability of the output.

5. Maintain clean/readable code: To ensure your code is tidy and straightforward to manage, divide lengthy or complex code into smaller, manageable parts.

Kickstart Your SQL Journey – For Free!

Discover how to craft powerful queries and oversee databases with our beginner-friendly free SQL course.

Explore Program

Conclusion

Understanding how to utilize the SQL SUBSTRING() function and its operations is crucial for effectively manipulating strings within database queries. It is immensely valuable for extracting, reformatting, and transforming text data, but it’s essential to recognize the limitations of SQL SUBSTRING() and the best techniques for using SQL SUBSTRING() effectively. Begin by practicing simple applications of the SQL SUBSTRING() function. As you gain comfort, blend it with other string functions like LEFT(), RIGHT(), and CHARINDEX() to augment your SQL query composition skills. This article has provided insights into the SQL SUBSTRING() function, its application, common pitfalls, and methods to evade them.

Elevate your skills today by enrolling in the SQL Course and acquiring hands-on experience. Additionally, prepare for job interviews with SQL Interview Questions curated by industry professionals.

SQL SUBSTRING – FAQs

The article SQL SUBSTRING was first published on Intellipaat Blog.

“`