SQL SUBSTRING Function: Extract Part of a String (All Databases)

Overview

SUBSTRING extracts a portion of a string starting at a given position for a specified length. The function name and syntax differ slightly across databases.

MySQL

-- SUBSTRING(string, start, length):
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'
SELECT SUBSTRING('Hello World', 7);     -- 'World' (to end)

-- Aliases: SUBSTR, MID all work the same
SELECT SUBSTR(email, 1, LOCATE('@', email) - 1) AS username
FROM users;

-- LEFT / RIGHT:
SELECT LEFT('Hello World', 5);   -- 'Hello'
SELECT RIGHT('Hello World', 5);  -- 'World'

PostgreSQL

-- SUBSTRING(string FROM start FOR length):
SELECT SUBSTRING('Hello World' FROM 1 FOR 5);  -- 'Hello'

-- Also supports standard syntax:
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'
SELECT SUBSTR('Hello World', 7);        -- 'World'

-- Extract with regex:
SELECT SUBSTRING('abc123def' FROM '[0-9]+');  -- '123'

-- LEFT / RIGHT:
SELECT LEFT('Hello World', 5);   -- 'Hello'
SELECT RIGHT('Hello World', 5);  -- 'World'

SQL Server

-- SUBSTRING(string, start, length):
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'

-- Length is required in SQL Server:
SELECT SUBSTRING(email, 1, CHARINDEX('@', email) - 1) AS username
FROM users;

-- LEFT / RIGHT:
SELECT LEFT('Hello World', 5);   -- 'Hello'
SELECT RIGHT('Hello World', 5);  -- 'World'

Oracle

-- SUBSTR(string, start, length):
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;  -- 'Hello'
SELECT SUBSTR('Hello World', 7) FROM DUAL;     -- 'World'

-- Extract username from email:
SELECT SUBSTR(email, 1, INSTR(email, '@') - 1) AS username
FROM users;

Skip the Syntax Lookup

Instead of memorizing SUBSTRING syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions