substring()

Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.

Signature

substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text
substring ( string text FROM pattern text ) → text
substring ( string text SIMILAR pattern text ESCAPE escape text ) → text
substring ( string text FROM pattern text FOR escape text ) → text

Examples

SELECT substring('Thomas' from 2 for 3);

hom

SELECT substring('Thomas' from 3);

omas

SELECT substring('Thomas' for 2);

Th

SELECT substring('Thomas' from '...$');

mas

SELECT substring('Thomas' similar '%#"o_a#"_' escape '#');

oma

Run against PostgreSQL 18.6 on 2026-09-13. This is the output we got, not the documentation’s.

In other engines

PostgreSQL	substring
MySQL	SUBSTRING(x, 2, 3)
SQL Server	SUBSTRING(x, 2, 3) — the length argument is required
Oracle	SUBSTR(x, 2, 3)
SQLite	substr(x, 2, 3)

SQL Server is the odd one: every other engine lets you omit the length and read to the end of the string, and SQL Server raises a syntax error.

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.