SUBSTR

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 7 of 27.

SUBSTR(s, start, length) extracts a piece of a string.

start is 1-indexed: the first character is position 1, not 0.
length is optional. Leave it off to get everything from start to the end.
A negative start counts from the end of the string.

SUBSTR('hello world', 1, 5)   -- 'hello'
SUBSTR('hello world', 7)      -- 'world'
SUBSTR('hello world', -5)     -- 'world'

Useful for pulling a year out of '2026-04-15', an area code out of a phone number, or the file extension off a path.

Challenge

Easy

Available tables and columns:
files: id, filename

Each filename is exactly four characters followed by a three-character extension (e.g. 'note.txt'). Return id, the four-character base as base, and the three-character extension as ext. Order by id.

Cheat sheet

SUBSTR(s, start, length) extracts a substring:

start is 1-indexed (first character = position 1)
length is optional — omit to get everything from start to the end
Negative start counts from the end of the string

SUBSTR('hello world', 1, 5)   -- 'hello'
SUBSTR('hello world', 7)      -- 'world'
SUBSTR('hello world', -5)     -- 'world'

Try it yourself

SELECT id,
       -- base = first 4 chars, ext = last 3 chars
FROM files
ORDER BY id

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Beyond the Basics

1Conditional Logic

CASE WHEN CASE in ORDER BY COALESCE for NULLs NULLIF and IIF Recap - Attendance

4Set Operations

UNION vs UNION ALL INTERSECT EXCEPT Recap - Membership

2String Functions

LENGTH, UPPER, LOWERSUBSTRINSTR REPLACE and TRIM Concatenating with ||Recap - Invoices

5Recursive CTEs

A Number Sequence Walking a Hierarchy Recap - Org Chart

3Conditional Aggregation

COUNT with CASE SUM with CASE Pivoting Rows to Columns Recap - Traffic Sources

6Analytics Recipes

Top-N per Group Percentage of Total Running Total Keeping One Row per Key Recap - Top Customers