SUBSTRING() in SQL

Extracts a portion of a string based on specified position and length.

Syntax

SUBSTRING(string FROM start_position [FOR length]) -- or 
SUBSTRING(string, start_position, length)

Return type

VARCHAR (or equivalent string type in the respective DBMS)

SUBSTRING() Function Example

SELECT SUBSTRING('Hello World', 1, 5);
-- Output: 'Hello'

What is SUBSTRING() in SQL?

The SUBSTRING() function in SQL extracts a specific portion of a string based on a given starting position and an optional length. It is widely used for manipulating text data, allowing users to retrieve sections of strings for formatting, filtering, or analysis. The function is available in databases like MySQL, PostgreSQL, SQL Server, and SQLite, though syntax may vary slightly. If the length parameter is omitted in some databases, the function returns the rest of the string. SUBSTRING() is useful for extracting names, parsing email addresses, truncating text, and isolating relevant data from larger text fields.

Parameters:

string: The source string to extract from
start_position: Starting position (1-based index in most DBMSs)
length: Optional number of characters to extract

Example Use Cases:

-- Extract first 3 characters of a name

SELECT SUBSTRING(first_name, 1, 3) AS name_prefix FROM users;
-- Output:
first_name    name_prefix
-----------   -----------
John             Joh
Maria             Mar
Robert           Rob

-- Extract year from date string

SELECT SUBSTRING('2024-01-01', 1, 4) AS year;
-- Output:
date_string    year
------------   ----
2024-01-01     2024
2023-12-25     2023
2024-03-15     2024
2023-06-30     2023
2024-09-01     2024

-- Extract domain from email

SELECT SUBSTRING(email, POSITION('@' IN email) + 1) AS domain FROM users;
-- Output:
email                      domain
-------------------------  ------------
john@gmail.com            gmail.com
maria@yahoo.com           yahoo.com
robert@hotmail.com        hotmail.com
sarah@company.org         company.org
james@outlook.com         outlook.com