SUBSTRING extraction in SQL

Introduction

We use SUBSTRING extraction to get a smaller part of a text from a bigger text. It helps us focus on just the piece we need.

You want to get the area code from a phone number stored as text.

You need to extract the first three letters of a product code.

You want to get the year from a date stored as text.

You want to shorten long text fields for display.

You want to check a part of a string for a pattern.

Syntax

SQL

SUBSTRING(string_expression FROM start_position FOR length)

start_position is where to begin extracting, counting from 1.

length is how many characters to take from the start_position.

Examples

Extracts 'Hello' starting at position 1 for 5 characters.

SQL

SELECT SUBSTRING('Hello World' FROM 1 FOR 5);

Extracts 'cd' starting at position 3 for 2 characters.

SQL

SELECT SUBSTRING('abcdef' FROM 3 FOR 2);

Extracts from position 2 to the end because length is longer than remaining text, returns 'hort'.

SQL

SELECT SUBSTRING('Short' FROM 2 FOR 10);

Start position is beyond string length, returns empty string.

SQL

SELECT SUBSTRING('Example' FROM 8 FOR 3);

Sample Program

This creates a table with product codes. Then it extracts the first 3 characters from each ProductCode.

SQL

CREATE TABLE Products (ProductID INT, ProductCode VARCHAR(20));
INSERT INTO Products VALUES (1, 'ABC123XYZ'), (2, 'DEF456'), (3, 'GHI7890');

SELECT ProductID, ProductCode, SUBSTRING(ProductCode FROM 1 FOR 3) AS CodePrefix FROM Products;

Important Notes

Time complexity is O(n) where n is the length of the substring extracted.

Space complexity is O(m) where m is the length of the substring returned.

If start_position is less than 1, some SQL systems treat it as 1.

If length is omitted in some SQL dialects, substring returns from start_position to end.

Use SUBSTRING when you need a fixed part of a string; use other functions like LEFT or RIGHT for simpler cases.

Summary

SUBSTRING extracts a part of a string starting at a position for a length.

Positions start counting at 1, not 0.

If you ask for more characters than available, it returns what it can.