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?
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
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:
- You need to extract a section of a string for data reporting.
- You want to clean or format raw text data.
- You are parsing specific fields from concatenated strings.
- You are processing unstructured text or logs.
- You need tight control of text data for the migration or ETL process.
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.
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.
Get 100% Hike!
Master Most in Demand Skills Now!
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 the employees whose department starts with FINANCE, which is “FIN.”
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
Function |
Performance Level |
Best Use Case |
SUBSTRING() |
Moderate, as it’s slightly slower in WHERE or JOIN conditions |
Retrieving a specific section of strings regardless of position. |
LEFT() |
High, as it’s optimized for fast filtering operations |
Returning characters from the beginning. |
RIGHT() |
High, as it performs equally well like LEFT() |
Extracting characters from the end. |
CHARINDEX() |
Moderate, as it’s quick on short strings but slower on long text |
Locating character positions quickly for pre-checks. |
LEN() |
Very High, as it’s fast and efficient in most use cases |
Measuring string length for validations or slicing. |
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 statements: 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.
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
Q1. What is the SUBSTRING() in SQL?
It’s a function that extracts a part of a string starting from a specified position and length.
Q2. How to cut part of a string in SQL?
Use SUBSTRING(column, start, length) to slice the desired portion from a string.
Q3. How does substr() work?
SUBSTR() works like SUBSTRING(), which is commonly used in databases like Oracle and SQLite to extract text.
Q4. How to get the first 10 characters from a string in SQL?
Use SUBSTRING(column, 1, 10) to return the first 10 characters.
Q5. What happens if the start position in SUBSTRING() exceeds the string length?
If the start position in SUBSTRING() exceeds the string length, most databases return an empty string without an error, but behavior can vary depending on the SQL system.