SQL SUBSTRING

String manipulation is a common task in SQL, and one of the most widely used functions for this purpose is SUBSTRING. It helps extract a specific portion of text from a larger string, making it especially useful when cleaning, formatting, or analyzing data. SQL SUBSTRING allows you to isolate specific characters from a string, enabling users to extract or report on targeted portions of the data. The function requires three main arguments, which are the string to extract from, the starting position of the extraction, and the number of characters to retrieve. In this article, you’ll learn what the SQL SUBSTRING function is and how it works with examples and their performance comparison.

Table of Contents:

• What is SQL SUBSTRING?
• Syntax and Parameters of SQL SUBSTRING
• When to Use SQL SUBSTRING?
• How to Use SQL SUBSTRING in Queries?
• Advanced Use Cases of SQL SUBSTRING
• SQL SUBSTRING vs Other String Functions
• Common SQL SUBSTRING Errors and How to Avoid Them
• Best Practices for Using SQL SUBSTRING
• Conclusion

What is SQL SUBSTRING?

SQL SUBSTRING is a built-in function used to fetch a particular part of a text string based on the starting point and a defined length. This is mostly supported by relational databases like MySQL, SQL Server, PostgreSQL, and Oracle, and is also used in data manipulation. This function plays an important role in data manipulation, cleaning, and transformation tasks. By using SUBSTRING, you can efficiently retrieve meaningful segments from larger text fields, making it easier to analyze and work with textual data.

Syntax and Parameters of SQL SUBSTRING

The general syntax of the SQL SUBSTRING() function: 

SUBSTRING(string_expression, start_position, length)

Explanation: 

• String_expression: The main string from which characters will be extracted.
• Start_position: The position to start extracting characters, based on a 1-based index (counting starts at 1, not 0).
• Length: The number of characters to extract starting from the start position.

Master SQL: Empower Your Data Skills Today!

Unlock the power of databases with hands-on SQL training and become a data-driven professional

Explore Program

When to Use SQL SUBSTRING?

Using SQL SUBSTRING() helps optimize data preparation and improve the quality of the data analysis, and it can be used when:

1. You need to extract a section of a string for data reporting.
2. You want to clean or format raw text data.
3. You are parsing specific fields from concatenated strings.
4. You are processing unstructured text or logs.
5. You need tight control of text data for the migration or ETL process.

1. Extracting Part of a String Using SQL SUBSTRING

Before extracting data, let’s first create a dataset to demonstrate 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;

Output:

This is the table after creation, and now to extract a portion of a string, you can use the SQL SUBSTRING() function to specify the starting position and length of the desired substring.

Example: 

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

Output:

Explanation: In this example, the SUBSTRING() extracted the first 6 characters from the full text by limiting the size of the string. 

2. Formatting or Cleaning Data with the SQL SUBSTRING

The SUBSTRING function in SQL is commonly used to format or clean data within a table. It is especially helpful when you need to extract only the relevant portion of a string while ignoring unnecessary details. In data warehousing and ETL projects, SUBSTRING() is often used to isolate specific parts of a value, such as extracting a date, code, or identifier from a larger text field. This function simplifies SQL string manipulation and improves overall data quality. Using SUBSTRING() effectively can reduce complexity and make data processing faster and more efficient.

Example:

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

Output:

Explanation: In this example, the SQL SUBSTRING() function fetched only the local number and removed the other numbers from the data. 

How to Use SQL SUBSTRING in Queries? 

The SQL SUBSTRING() function allows you to extract a particular portion of a string based on start position and length. It is basically used in queries for parsing, cleaning, and analyzing text data. 

Let’s start by creating a sample dataset that we can use to demonstrate how the SQL SUBSTRING() function works in different 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;

Output:

This is a sample output shown after creating the table, which displays the data stored in it.

1. Using SQL SUBSTRING with String Literals

You can use SUBSTRING() directly on a string to extract the desired part of the data from the string. 

Example:

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

Output:

Explanation: In this example, the SUBSTRING() extracted the string from the start position 9 up to a length of 5 characters.

2. Using SQL SUBSTRING with Column Values

SUBSTRING() can be used in column values to extract data like names, codes, and also numbers from the text that is stored in a table.

Example:

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

Output:

Explanation: In this example, the SUBSTRING() function fetched only the local number from the full phone number of the employees. 

3. Using SQL SUBSTRING in WHERE Clause

You can use a SUBSTRING() with a WHERE Clause to filter the strings based on conditions. It will be very useful when the data contains codes or structured text. 

Example:

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

Output: 

Explanation: In this example, the WHERE clause fetched the details of employees whose department names start with “FIN”, matching departments like “FINANCE”.

4. Using SQL SUBSTRING in JOIN Conditions

The JOIN clause can be combined with SUBSTRING() to join two tables based on matching parts of their columns. Using the example below, join the employees table to get the desired output.

Example:

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;

Output:

Explanation: In this example, the JOIN clause combined two tables with respect to their departments. The employee “Karan” is missing because the department prefix “HR” may not match due to data inconsistencies or formatting issues in the JOIN condition.

Advanced Use Cases of SQL SUBSTRING

The SUBSTRING() function in SQL can also be applied in advanced scenarios, such as dynamic joins, conditional filtering, and targeted updates based on partial string matches.

1. Using SQL SUBSTRING on Table Columns

The SQL SUBSTRING() extracts a part of a string from a table column with the help of the start position and length. It is useful when you want to structure text like ID, date, and codes within a larger string. 

Example:

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

Output:

Explanation: In this example, the SQL SUBSTRING() function extracts the name and department code by starting from position 1 and taking the first 3 characters as a prefix.

2. SQL SUBSTRING for Updating Column Values

We can use SQL SUBSTRING for updating column values with the help of the UPDATE statement to manipulate the data in a table. 

Example: If you want to alter or update the contact number of an employee to hide their details, you can use the UPDATE statement. 

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

Output:

Explanation: In this example, the phone number of an employee, “Karan”, has been changed to “XXXXX” to hide their phone number using an UPDATE statement with the SUBSTRING() function. 

SQL SUBSTRING vs Other String Functions

These are the differences between SQL SUBSTRING() and other String functions. But avoid these functions if you want to work inside a WHERE or JOIN Clause whenever possible to avoid compromising performance on large datasets. 

Common SQL SUBSTRING Errors and How to Avoid Them

There are some errors when using SQL SUBSTRING(). Below are the common errors with solutions on how to avoid them.

1. Incorrect Usage of Datatype

Error: This will raise an error if the datatype has integers instead of strings.
To Avoid: First, convert the integer value to a VARCHAR type by using CAST(column AS VARCHAR). This allows you to perform string operations like SUBSTRING() on the converted data.

2. Invalid Starting Position or Length

Error: If the start position specified in SUBSTRING() is beyond the length of the string, SQL Server, MySQL, and PostgreSQL return an empty string rather than an error, while Oracle raises an error.
To Avoid: make sure you use the LEN() function to check the length before using SUBSTRING().

3. Using NULL Without Handling It

Error: If you mention NULL in the text part, then the SUBSTRING will return NULL.
To Avoid: Use the COALESCE function to get a NULL value safely in the SUBSTRING().

4. Assuming SQL Uses 0-Based Indexing

Error: SQL uses a 1-based index, so make sure not to assume zero-based indexing like in some programming languages.
To Avoid: Make sure you always mention that the counting has to start from a value.

5. Unexpected Results in JOINs

Error: The error will occur if the two join tables don’t have the same output.
To Avoid: Check if the two tables are compatible. If they are not, use the TRIM() or CAST() function where necessary.

Best Practices for Using SQL SUBSTRING

To effectively and efficiently use the SQL SUBSTRING() function in practice, here are some best practices to follow:

1. Always Validate Your Input: Use functions like LEN() or CHARINDEX() to ensure extraction will not generate an error.

2. Combine the SUBSTRING() function with other SQL string functions: Using SUBSTRING() with dynamic content is effective when you use it with CHARINDEX(), LEFT(), or RIGHT() together in the process.

3. Use with caution in WHERE and JOIN Clauses: Avoid using SUBSTRING() in WHERE or JOIN clauses on large tables because it slows down the query performance.

4. Alias your output clearly: Use clear aliases for your result columns, such as AS DeptPrefix, to make the output easier to understand.

5. Keep your code clean/readable: To make your code cleaner and easier to maintain, break long or complex code into smaller, manageable sections.

Kickstart Your SQL Journey – For Free!

Learn how to write powerful queries and manage databases with our beginner-friendly free SQL course.

Explore Program

Conclusion

Knowing how to use the SQL SUBSTRING() function and how it works is essential to effectively manipulate strings in database queries. It is very helpful in extracting, reformatting, and transforming text data, but you need to know the limits of SQL SUBSTRING() and best practices for using SQL SUBSTRING() effectively. Start by practicing simple examples of the SQL SUBSTRING() function. Once comfortable, combine it with other string functions like LEFT(), RIGHT(), and CHARINDEX() to enhance your SQL query writing skills. In this article, you have learned about the SQL SUBSTRING() function, its usage, and its common errors, and how to avoid them. 

Take your skills to the next level by enrolling in the SQL Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts.

SQL SUBSTRING – FAQs