SQL String Functions

SQL LEFT and RIGHT Functions: Extract Characters (All Databases)

SQL LEFT and RIGHT functions to extract characters from the start or end of a string. Covers all databases with practical examples.

Mar 12, 20263 min read

Overview

LEFT returns the first N characters from a string, RIGHT returns the last N. Simpler than SUBSTRING when you just need characters from either end.

MySQL / PostgreSQL / SQL Server

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

-- Get file extension:
SELECT RIGHT(filename, 3) AS extension FROM files;

-- Mask credit card (show last 4):
SELECT CONCAT('****-****-****-', RIGHT(card_number, 4)) AS masked
FROM payments;

-- Get area code from phone:
SELECT LEFT(phone, 3) AS area_code FROM contacts;

-- First initial + last name:
SELECT CONCAT(LEFT(first_name, 1), '. ', last_name) FROM users;

Oracle

-- Oracle doesn't have LEFT/RIGHT — use SUBSTR:
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;   -- 'Hello' (like LEFT)
SELECT SUBSTR('Hello World', -5) FROM DUAL;     -- 'World' (like RIGHT)

-- Negative position counts from the end:
SELECT SUBSTR(phone, -4) AS last_four FROM contacts;

Skip the Syntax Lookup

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

Try AI2SQL Free

No credit card required

Frequently Asked Questions

How do I get the first N characters of a string in SQL?

Use LEFT(string, N) in MySQL, PostgreSQL, and SQL Server. In Oracle, use SUBSTR(string, 1, N).

Does Oracle have LEFT and RIGHT functions?

No. Oracle uses SUBSTR instead. SUBSTR(string, 1, N) replaces LEFT, and SUBSTR(string, -N) replaces RIGHT.

Can AI2SQL extract parts of strings?

Yes. Say 'get the first 3 characters of the zip code column' and AI2SQL generates the correct query for your database.