When working with SQL, manipulating strings is a fundamental task. Whether you’re extracting certain parts of a string, cleaning up data, or formatting results, SQL provides many built-in functions for these tasks. One of the most commonly used functions for string manipulation is SUBSTRING().
What is SUBSTRING in SQL?
The SUBSTRING() function in SQL is used to extract a portion of a string based on a specified starting position and length. It works across different SQL databases such as MySQL, PostgreSQL, SQL Server, and Oracle, making it an essential tool for anyone working with SQL queries.
Basic Syntax of SUBSTRING
The general syntax of the SUBSTRING() function is as follows:
SUBSTRING(string, start_position, length)Here’s what each parameter means:
- string – The source string from which we want to extract substrings.
- start_position – The position where extraction should begin (starting from 1).
- length – The number of characters to extract.
Simple Example of Using SUBSTRING
Let’s take a look at a basic example:
SELECT SUBSTRING('Hello World', 1, 5) AS ExtractedString;The result of this query would be:
| ExtractedString |
|---|
| Hello |
Here, we start at position 1 and extract 5 characters, resulting in “Hello“.
Using SUBSTRING with a Table Column
Most of the time, instead of extracting text from a static string, we use SUBSTRING() on columns in a table. Suppose we have the following table named employees:
| id | full_name |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
Now, if we want to extract just the first name from the full_name column, we can use:
SELECT SUBSTRING(full_name, 1, CHARINDEX(' ', full_name) - 1) AS FirstName
FROM employees;This query will return:
| FirstName |
|---|
| John |
| Jane |
Advanced Use Cases for SUBSTRING
Sometimes, SUBSTRING is used in more advanced scenarios. Here are some examples:
1. Extracting Domain Name from an Email Address
Given a table users, where the email column contains email addresses like user@example.com, we can extract the domain name:
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS Domain
FROM users;2. Masking Sensitive Data
If you want to display only the last four digits of a credit card number stored in the column card_number, you can use:
SELECT CONCAT('**** **** **** ', SUBSTRING(card_number, LEN(card_number) - 3, 4)) AS MaskedCard
FROM payments;3. Extracting Year from a Date String
For a purchase_date column containing values like 2024-06-12, extracting the year can be done like this:
SELECT SUBSTRING(purchase_date, 1, 4) AS Year
FROM orders;SUBSTRING vs. LEFT, RIGHT, and MID
While SUBSTRING is very flexible, SQL also provides alternative functions for extracting part of a string:
- LEFT(string, length) – Extracts the leftmost characters.
- RIGHT(string, length) – Extracts the rightmost characters.
- MID(string, start, length) (MySQL only) – Works just like SUBSTRING.
For example, to extract the first three letters of a name:
SELECT LEFT(full_name, 3) FROM employees;Performance Considerations
While SUBSTRING can be powerful, excessive use in a query—especially on large datasets—can impact performance. Here are some tips to optimize it:
- Avoid applying SUBSTRING on indexed columns in WHERE clauses, as it can prevent index usage.
- Consider storing pre-computed substrings in separate columns if frequently used.
- Use database-specific indexing techniques where necessary for optimization.
Conclusion
The SUBSTRING() function in SQL is a versatile tool for extracting parts of a string. Whether you’re retrieving names, formatting data, or working with text manipulation, understanding how SUBSTRING() works will make your SQL queries more efficient and powerful.
Other interesting article:
How RTRIM works in SQL? Best RTRIM examples