Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL SUBSTR() and SUBSTRING() functions

Reference for SQL SUBSTR() and SUBSTRING(): extract part of a string by start position and length, negative positions, edge cases and dialect differences.

SUBSTR(s, start, length) returns a piece of a string, beginning at a position and running for a number of characters. Positions are 1-based: the first character is position 1. It is the main tool for cutting fixed-width codes apart, taking prefixes and building values such as initials.

The standard spelling is SUBSTRING(s FROM start FOR length). Most databases also accept a comma form, and several offer the short name SUBSTR. SQLite understands both substr() and substring() (the latter since version 3.34).

SQL
SELECT SUBSTR('abcdef', 2, 3) AS piece;   -- bcd

Try it

SQL Start, length and negative positions

Press Run (or Ctrl + Enter) to see the result here.

SQL Initials and the domain of an email ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
SUBSTR(string, start [, length])
SUBSTRING(string FROM start [FOR length])
SUBSTRING(string, start [, length])
Name Description
string The text to cut from.
start Position of the first character to take; 1 is the first character.
length Optional. How many characters to take. Without it, you get everything to the end.

Returns: the extracted text, or an empty string if nothing is left. NULL if any argument is NULL.

Dialect differences

Database Function Negative start Notes
SQLite SUBSTR, SUBSTRING counts from the end start 0 behaves like a position before the string, so substr('abcdef', 0, 3) is ab
PostgreSQL SUBSTRING(s FROM a FOR n), SUBSTR(s, a, n) positions before 1 use up the length a negative length is an error
MySQL SUBSTRING, SUBSTR, MID counts from the end start 0 gives an empty string
SQL Server SUBSTRING(s, a, n) no end-counting length is required; use RIGHT(s, n) for the end
Oracle SUBSTR(s, a [, n]) counts from the end start 0 is treated as 1
SQL
-- PostgreSQL
SELECT SUBSTRING('abcdef' FROM 2 FOR 3);   -- bcd

-- SQL Server: length is required
SELECT SUBSTRING('abcdef', 2, LEN('abcdef'));

Usage notes

  • Start at 1, not 0. Passing 0 gives different results in different databases; avoid it.
  • Negative start (SQLite, MySQL, Oracle) counts from the right end: SUBSTR(s, -3) is the last three characters. PostgreSQL and SQL Server need RIGHT(s, 3) or SUBSTRING(s, LENGTH(s) - 2).
  • A length longer than the string is fine; you just get what is there.
  • A negative length in SQLite takes characters before the start: substr('abcdef', 2, -1) is a. Other databases error or return an empty string.
  • Combine with INSTR() to cut around a delimiter: take up to the first space with SUBSTR(s, 1, INSTR(s, ' ') - 1). If the delimiter is missing, INSTR returns 0, so check first.
  • Characters, not bytes, for text values (blobs are measured in bytes in SQLite).
  • Cutting on a column in WHERE (SUBSTR(code, 1, 2) = 'AB') cannot use a normal index; prefix searches with LIKE 'AB%' can.