Edge Extractions

Concepts covered: Substrings

The most common SUBSTR calls read from one end of a value: the first few characters or the last few. The start needs no arithmetic: a start of 1 and a count of n returns the first n characters, which is how the country code came out of the ISRC. The end is harder, because values differ in length and the position of the last character changes from row to row. A bank account number with 22 characters ends at position 22; one with 18 characters ends at 18. A fixed start of 19 returns the last 4 for the 22-character account and nothing for the 18-character one, whose last 4 begin at 15.

SUBSTR answers with a negative start, counted from the right end: -1 is the last character, -2 the one before it, and SUBSTR(s, -n) returns the last n characters whatever the length of s.

Characterbeatbox.io
From the start12345678910
From the end-10-9-8-7-6-5-4-3-2-1

SUBSTR('beatbox.io', -2) returns io, the last 2 characters. SUBSTR('beatbox.io', -3, 1) starts 3 from the end and takes 1 character, which is the dot. The count still runs left to right from the start position, so a negative start with a length never reads backwards.

3 ways to take the last 4

A payout export lists each artist's bank account, and the report may show only the last 4 characters. The switcher holds 3 versions of the same query. The first uses a negative start. The second computes the start from LENGTH: for a 22-character account, LENGTH minus 3 is 19, and positions 19 to 22 are the last 4. The third subtracts 4 instead of 3, the off-by-one that returns 5 characters; run it and count.

payouts
artist_namebank_account
Low TideDE89370400440532013000
Paper MoonsGB29NWBK60161331926819
Static BloomNL91ABNA0417164300

Negative

A negative start counts from the right end, so -4 lands on the fourth character from the end for every row regardless of its length.

SELECT
  artist_name,
  CONCAT('****', SUBSTR(bank_account, -4)) AS masked_account
FROM payouts
Result
artist_namemasked_account
Low Tide****3000
Paper Moons****6819
Static Bloom****4300

LENGTH math

LENGTH minus 3 is the position of the fourth character from the end, so this version returns the same 4 characters with more arithmetic.

SELECT
  artist_name,
  CONCAT('****', SUBSTR(bank_account, LENGTH(bank_account) - 3)) AS masked_account
FROM payouts
Result
artist_namemasked_account
Low Tide****3000
Paper Moons****6819
Static Bloom****4300

Off by one

LENGTH minus 4 is the position of the fifth character from the end, so this version leaks one extra digit in every row.

SELECT
  artist_name,
  CONCAT('****', SUBSTR(bank_account, LENGTH(bank_account) - 4)) AS masked_account
FROM payouts
Result
artist_namemasked_account
Low Tide****13000
Paper Moons****26819
Static Bloom****64300

When the count is larger than what remains, SUBSTR hands back whatever is left, so SUBSTR('LT', 1, 3) is LT. For display that is harmless. For validation it hides a fault: a currency code that should read EUR arrives as EU, passes through every SUBSTR in the query, and nothing marks it short. LENGTH is the check when the length matters, and WHERE LENGTH(currency_code) != 3 lists the short codes before any SUBSTR runs on them.

SUBSTR or LIKE

Filtering already has a tool for edges. WHERE isrc LIKE 'US%' keeps the rows whose ISRC starts with US, and it keeps the same rows as WHERE SUBSTR(isrc, 1, 2) = 'US'. The 2 differ in what they produce. LIKE answers yes or no per row; SUBSTR produces a value, and a value can be selected, grouped, joined and sorted.

Reach for LIKE
  • The question is whether a row matches
  • The pattern is in WHERE and nowhere else
  • Wildcards in the middle of the pattern
Reach for SUBSTR
  • The piece itself becomes a column
  • GROUP BY or JOIN on the piece
  • The piece is a fixed count from an edge

The query counts the delivery's tracks per country of registration. GROUP BY accepts the expression directly, and the alias in SELECT names the column the reader sees.

SELECT
  SUBSTR(isrc, 1, 2) AS country,
  COUNT(*) AS track_count
FROM tracks
GROUP BY SUBSTR(isrc, 1, 2)
ORDER BY track_count DESC, country
tracks
track_nameisrc
Night DriveUSRC17607839
Paper MoonsGBUM71029601
Static BloomSEBXP2200418
Low TideBRBMG2300711
Glass HarborUSAT22101204
Copper SkyGBAYE2000317
Result
countrytrack_count
GB2
US2
BR1
SE1

Edge extraction has a limit that shows up as soon as the count varies. Audio file names in the delivery end in .flac or .mp3. SUBSTR(file_name, -4) returns flac for the first and .mp3 for the second, because the extension's length differs. A fixed count from either edge cannot follow a separator that moves; STRPOS, 2 sections on, finds it.

What does SUBSTR('night_drive.flac', -4) return?

Masking has 2 edges of its own. An account shorter than 4 characters has no fourth character from the end, and a negative start that reaches beyond the first character yields an empty string, so CONCAT('****', SUBSTR('A12', -4)) prints only the 4 asterisks: nothing leaks, and nothing shows the value was short. A NULL account makes SUBSTR return NULL, and CONCAT with a NULL argument returns NULL, so the masked column is NULL for that artist, which tells the payout report the account is missing rather than showing a mask over nothing.

The payout report lists each artist name from payouts beside a masked account: 4 asterisks followed by the last 4 characters of bank_account, whatever the account's length. Put the pieces in the order that builds the query.

About this lesson

This section is part of the String Functions: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.