SQL SUBSTRING

What is the SQL SUBSTRING function?

Returns a portion of a string starting at a specified position for an optional length.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SUBSTRING: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle (SUBSTR), SQLite, Snowflake, BigQuery, Redshift

SQL SUBSTRING Full Explanation

SUBSTRING is a scalar string-manipulation function defined in the SQL standard and implemented by virtually every major database. It extracts a contiguous sequence of characters from a source string based on a 1-based start position and, optionally, a length. If the length argument is omitted, the function returns the rest of the string from the start position to the end.Two syntactic forms exist:1. Standard form: SUBSTRING(source FROM start [FOR length])2. Functional form used by many vendors: SUBSTRING(source, start, length)Behavioral notes:- Indexing is 1-based in all common dialects.- If start_position is less than 1 the result is implementation-defined (most return an empty string).- If start_position exceeds the source length, the result is an empty string.- If length is negative or zero, most systems raise an error.- Binary data variants (SUBSTRING for BYTEA/BLOB) follow the same rules.Differences by dialect:- Oracle and SQLite expose the same capability under the alias SUBSTR, but also accept SUBSTRING.- SQL Server requires the functional form with all three arguments.- PostgreSQL accepts both forms and supports extraction from bytea and bit strings.Performance is O(n) in the length requested and generally inexpensive because no scanning beyond the requested region is performed.

SQL SUBSTRING Syntax

-- Standard SQL
SUBSTRING(source_string FROM start_position [FOR substring_length]);

-- Functional alternative
SUBSTRING(source_string, start_position [, substring_length]);

SQL SUBSTRING Parameters

source_string (string) - the input text or byte sequence
start_position (integer) - 1-based index of the first character to extract
substring_length (integer, optional) - number of characters to return. If omitted, returns to end of string

Example Queries Using SQL SUBSTRING

-- PostgreSQL style
SELECT SUBSTRING('Galaxy' FROM 1 FOR 3);      -- returns 'Gal'

-- MySQL style (length omitted)
SELECT SUBSTRING('Galaxy', 4);                -- returns 'laxy'

-- SQL Server style with column data
SELECT SUBSTRING(ProductName, 1, 10) AS ShortName
FROM products;

Expected Output Using SQL SUBSTRING

Each query returns a single text column:1
'Gal'2
'laxy'3
For every row in products, the first 10 characters of ProductName in the alias ShortName

Use Cases with SQL SUBSTRING

Truncating long text for previews or UI snippets
Parsing fixed-width files or codes (e.g., get year from '2023-05-10')
Extracting prefixes or suffixes for classification
Creating search keys or indexable substrings
Cleaning or normalizing heterogeneous data

Common Mistakes with SQL SUBSTRING

Treating the start_position as 0-based instead of 1-based
Forgetting that SQL Server requires all three arguments
Mixing up argument order in the functional form (length before start)
Omitting the FOR keyword in the standard form
Expecting negative start positions to count from the end (use RIGHT or custom logic instead)

First Introduced In

SQL-92

Frequently Asked Questions

How do I extract the last n characters of a string?

Use SUBSTRING with LENGTH:```sqlSELECT SUBSTRING(col FROM LENGTH(col) - 3 + 1 FOR 3) FROM t;```

Can I omit the length argument?

Yes. If you only supply source and start, SUBSTRING returns from the start position to the end of the string in dialects that allow the two-argument form (e.g., MySQL, PostgreSQL).

Does SUBSTRING work on binary or bytea data?

In PostgreSQL, SQL Server, and others, SUBSTRING operates on binary types as well, following the same rules.

Why does my SUBSTRING call fail in SQL Server when I omit the length?

SQL Server's syntax requires all three arguments. Provide the length or switch to LEFT/RIGHT for simpler use cases.