SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Extracting substrings by position
Pull fixed-position fields out of strings with SUBSTRING, LEFT, and RIGHT, and predict exactly what happens when the requested window runs past either end.
What you will learn
- Use SUBSTRING(s FROM start FOR count); positions start at 1, not 0
- Omit FOR to take everything from a start position to the end of the string
- Predict clamping: out-of-range windows shrink the result and never raise an error
- Negative starts clamp to 1 in Postgres but count from the end in MySQL/Oracle/SQLite
Understanding Extracting substrings by position
Positional extraction means naming a starting character position and a count: substring(code from 4 for 2) reads two characters beginning at the fourth. SQL numbers characters from 1, so the first character sits at position 1 and the last at position length(s). Leaving off FOR takes everything from the start position through the end, and left(s, n) and right(s, n) are the anchored shorthands for the two ends, with right() the only one of the three that counts backwards.
The reliable mental model is a window intersected with reality: substring asks for positions start through start + count - 1, then keeps only the positions that actually exist in 1..length(s). That single rule explains three separate surprises. A count larger than what remains gives you what remains, a start past the end gives the empty string rather than NULL or an error, and a start below 1 spends part of the count on positions that were never there, which is why substring('abcdef' from -1 for 4) returns 'ab' and not 'abcd'.
Positions are trustworthy only when the data format guarantees them: IBANs, fixed-width exports, part numbers with zero-padded segments. When offsets merely happen to line up in the rows you sampled, positional extraction fails silently and returns a plausible wrong string instead of complaining, so test against your shortest and longest real values. These functions count characters rather than bytes in PostgreSQL, so accented or CJK text keeps its boundaries, while byte-oriented variants in other engines can cut a multibyte character in half.
-- PostgreSQL
SELECT code,
substring(code from 1 for 3) AS region,
substring(code from 4 for 2) AS yr,
substring(code from 6) AS serial,
right(code, 4) AS last4
FROM (VALUES ('NYC24A0917'), ('SFO23B0042')) AS t(code);SUBSTRING returns the characters whose positions fall inside both the window you asked for and the range 1..length(s), which is why an out-of-range request quietly shrinks the result instead of failing.
Worked examples
Splitting a fixed-layout code
Parses the guaranteed segments of a UK IBAN by their known offsets and widths.
-- PostgreSQL
SELECT substring(iban from 1 for 2) AS country,
substring(iban from 3 for 2) AS check_digits,
substring(iban from 5 for 4) AS bank,
substring(iban from 9) AS account
FROM (VALUES ('GB29NWBK60161331926819')) AS t(iban);Example explained
Line 1The country field starts at 1 because SQL positions are 1-based, so 'G' is at position 1.
Line 2Each later start is the previous start plus the previous width: 1+2=3, 3+2=5, 5+4=9.
Line 3The final field drops FOR, so it runs to the end and survives IBANs of different total lengths.
Line 4This works only because the IBAN standard fixes those offsets; nothing here is derived from the data.
What happens at the edges
Shows the clamping rule for a count that overruns, a start past the end, and a start below 1.
SELECT substring('abcdef' from 4 for 10) AS past_end,
substring('abcdef' from 10 for 3) AS beyond,
substring('abcdef' from -1 for 4) AS before_start,
length(substring('abcdef' from 10 for 3)) AS len_beyond;Example explained
Line 1past_end asks for positions 4 through 13 but only 4, 5 and 6 exist, so it yields 'def'.
Line 2beyond asks for positions 10 through 12, none of which exist, so the result is '' and its length is 0, not NULL.
Line 3before_start asks for positions -1, 0, 1 and 2; the first two do not exist, so two of the four requested characters are simply lost.
Line 4No case raises an error, which is exactly why a miscalculated start can go unnoticed in production queries.
Taking the tail portably
Contrasts right() with a negative start, whose meaning differs between engines.
-- PostgreSQL
SELECT right('ABCDEF', 3) AS tail_right,
substr('ABCDEF', -3) AS pg_negative,
substring('ABCDEF' from length('ABCDEF') - 2) AS tail_computed;Example explained
Line 1right('ABCDEF', 3) counts from the back and clamps safely: on a two-character value it returns those two characters instead of erroring.
Line 2substr('ABCDEF', -3) returns the whole string in PostgreSQL because the start is clamped to 1; MySQL, Oracle and SQLite read -3 as 'third character from the end' and return 'DEF'.
Line 3tail_computed derives the start as length - 2 so the arithmetic, not a dialect convention, decides where the tail begins.
Line 4Prefer right() or a computed start when the same SQL has to run on more than one engine.
Important notes
A window that misses the string entirely produces '', not NULL, so IS NULL guards never fire; test length(...) = 0 or = '' instead.
SQL Server has no FROM/FOR form and requires all three arguments, and positional overwriting is OVERLAY(s PLACING 'XXXX' FROM 1 FOR 4) rather than REPLACE.
Common mistakes
Starting at 0 out of habit from Python or JavaScript: substring(code from 0 for 3) requests positions 0, 1 and 2, position 0 is discarded, and you silently get a two-character 'NY' instead of 'NYC'.
Reading the third argument as an end position: substr(code, 4, 6) intended as 'characters 4 through 6' actually returns six characters starting at 4, so the field swallows part of the next one and still looks like valid data.
Copying substr(col, -4) from an Oracle or MySQL answer into PostgreSQL to get the last four characters: Postgres clamps the start to 1 and returns the entire value, so a masking or matching query quietly passes full card or account numbers through.
Try it yourself
Change, predict, then run
Using only substring, left and right, turn 'ORD-2026-000581-EU' into four columns: prefix, year, the six-digit serial, and the two-letter region. Then run the same query against 'ORD-2026-7-EU' and say which columns break and why no error is raised.
Open the SQL workspaceCheck your understanding
In PostgreSQL, substring('PROD-42' from -2 for 5) returns 'PR'. Why only two characters when the count is 5?
- The requested window covers positions -2 through 2, and only positions 1 and 2 exist, so three of the five requested positions have nothing to contribute
- A negative start makes SUBSTRING count backwards from the end, so it returns the last two characters of the string
- The count is reduced to length(string) - 5, which leaves 2 characters
- SUBSTRING pads missing positions with NULL and the padding is stripped from the result
Show answer
The window runs from start to start + count - 1, here -2 to 2, and is intersected with the positions that exist, 1 to 7; positions -2, -1 and 0 yield nothing and the window ends at 2, so 'PR' comes back. Option 2 describes Oracle, MySQL and SQLite substr, and even under that rule the answer would be '42'; getting 'PR' proves PostgreSQL clamped the start to 1 rather than counting from the end.