String Functions: Intermediate
A string is a row of characters, and every character sits at a numbered position that starts from 1 at the left. SUBSTR reads a run of those positions and returns it as a new string. The function takes 3 arguments: the string to read, the position to start at, and how many characters to take. SUBSTR('USRC17607839', 1, 2) starts at position 1, takes 2 characters, and returns US. The original value is untouched; the result is a new string in its own column.
| Position | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Character | U | S | R | C | 1 | 7 | 6 | 0 | 7 | 8 | 3 | 9 |
- ▸Country: SUBSTR(isrc, 1, 2) returns US
- ▸Registrant: SUBSTR(isrc, 3, 3) returns RC1
- ▸Year: SUBSTR(isrc, 6, 2) returns 76
- ▸Designation: SUBSTR(isrc, 8, 5) returns 07839
SUBSTRING is the same function under its full name, and SUBSTRING(isrc, 1, 2) returns exactly what SUBSTR(isrc, 1, 2) does. The third argument is optional: leave it out and SUBSTR returns everything from the start position to the end of the string, so SUBSTR(isrc, 8) is a second way to write the designation.
SELECTtrack_name,SUBSTR(release_date, 1, 4) AS release_year,SUBSTR(release_date, 6, 2) AS release_monthFROM releases
The day is the last 2 characters, so SUBSTR(release_date, 9, 2) or the shorter SUBSTR(release_date, 9) returns 29 for the first row. Once each part is its own column, a CAST turns the text into a number, and the month can be grouped, compared and sorted like any other integer.
- ▸A length that runs past the end returns the rest of the string: SUBSTR('Low Tide', 5, 50) returns Tide
- ▸A start past the end returns an empty string: SUBSTR('Low Tide', 20, 2) returns ''
- ▸A start of 0 returns an empty string, because positions begin at 1
- ▸A NULL in any argument returns NULL, the same way CONCAT and LENGTH behave
What does this expression return?
When the layout slips
Fixed positions are only as reliable as the layout behind them. A date typed by hand as 2019-1-29, with no leading zero on the month, is 9 characters long, and every field after the year moves 1 position to the left. The month call then returns 1- and the day call returns 9, and neither raises an error. A well-formed date is exactly 10 characters, with hyphens at positions 5 and 8, so WHERE LENGTH(release_date) != 10 lists every row the fixed positions would misread before any of them reaches a report.
| release_date | LENGTH | SUBSTR(release_date, 6, 2) | SUBSTR(release_date, 9, 2) |
|---|---|---|---|
| 2019-11-29 | 10 | 11 | 29 |
| 2019-1-29 | 9 | 1- | 9 |
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.
| Character | b | e | a | t | b | o | x | . | i | o |
|---|---|---|---|---|---|---|---|---|---|---|
| From the start | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| 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
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.
- The question is whether a row matches
- The pattern is in WHERE and nowhere else
- Wildcards in the middle of the pattern
- The piece itself becomes a column
- GROUP BY or JOIN on the piece
- The piece is a fixed count from an edge
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.
REPLACE takes a string, a search text and a replacement, and returns the string with every occurrence of the search text swapped for the replacement. Position plays no part: REPLACE scans the whole value from left to right and swaps each match it meets. REPLACE('Night Drive (feat. Low Tide)', 'feat.', 'ft.') returns Night Drive (ft. Low Tide). An empty replacement deletes the search text: REPLACE(amount_text, ',', '') removes every comma, and the string shrinks by 1 character per comma removed.
Cleaning numbers that arrived as text
4 rules that decide the result
- ▸Every occurrence is replaced, from the first to the last: REPLACE('a,b,c', ',', ';') returns a;b;c
- ▸Matching is case-sensitive: REPLACE('The Beatles', 'the', '') leaves the value unchanged
- ▸Matches do not overlap: REPLACE('aaa', 'aa', 'b') returns ba, since the first 2 characters match and the third stands alone
- ▸A NULL in any argument returns NULL
Because REPLACE swaps every occurrence, it also swaps occurrences inside longer words. A genre column holds pop, Britpop and hip hop, and REPLACE(genre, 'pop', 'Pop') capitalises the first and turns the second into BritPop, because the 3 characters match inside the longer word. REPLACE has no idea of words; it sees characters. When a match must stand alone, compare the whole value with = or CASE instead of replacing inside it.
Order matters when calls are nested
| Expression | Result |
|---|---|
| REPLACE(REPLACE('a-b_c', '-', '_'), '_', '-') | a-b-c |
| REPLACE(REPLACE(REPLACE('a-b_c', '-', '#'), '_', '-'), '#', '_') | a_b-c |
The same chaining builds the URL slug for a track page, where a title becomes lowercase text joined by hyphens. In REPLACE(LOWER(title), ' ', '-') the LOWER call is innermost, so REPLACE receives a value that is already lowercase. The second row below shows the limit of that slug: the parentheses and the dot survive, because REPLACE changes only the text it is given. Each further character to remove needs one more call wrapped around the outside, and that call sees everything the inner calls wrote.
| title | REPLACE(LOWER(title), ' ', '-') |
|---|---|
| Glass Harbor | glass-harbor |
| Night Drive (Feat. Low Tide) | night-drive-(feat.-low-tide) |
What does this expression return?
Same calls, different export
The 2 REPLACE calls in the royalty query fit one format: a dollar sign in front, commas between thousands, a dot before the cents. The second row below arrives with a currency word that neither call names, so the cleaned text still holds letters and TRY_CAST returns NULL. The third row is worse. A European export writes 1,250.00 as 1.250,00, the comma call deletes its decimal mark, and 1.25000 casts cleanly to 1.25: a royalty 1,000 times too small, with no step failing. Before trusting the cast, count the rows where TRY_CAST returns NULL, and check that every raw amount matches LIKE '$%'.
| amount_text | After both REPLACE calls | TRY_CAST AS DECIMAL(10, 2) |
|---|---|---|
| $1,250.00 | 1250.00 | 1250.00 |
| USD 1,250.00 | USD 1250.00 | NULL |
| 1.250,00 | 1.25000 | 1.25 |
Every SUBSTR so far was given a position that was known before the query ran: a standard fixed it, or every value shared one layout. Email addresses break that. The @ sits at position 4 in ana@beatbox.io and at position 11 in dev.okafor@soundmail.com, so no fixed start separates the name from the domain. STRPOS finds the position. STRPOS(string, search) returns the position of the first occurrence of search, counted from 1, and returns 0 when search appears nowhere in the string.
| STRPOS(email, '@') | |
|---|---|
| ana@beatbox.io | 4 |
| dev.okafor@soundmail.com | 11 |
| support-team | 0 |
A position is most useful as input to SUBSTR, and the 2 functions pair up in 2 patterns. Everything before the separator is SUBSTR(email, 1, STRPOS(email, '@') - 1): start at 1 and take 1 character fewer than the separator's position. Everything after it is SUBSTR(email, STRPOS(email, '@') + 1): start 1 past the separator and take the rest. Play the animation and follow the arithmetic row by row.
SELECTemail,STRPOS(email, '@') AS at_pos,SUBSTR(email, STRPOS(email, '@') + 1) AS domainFROM listeners
The third row shows the trap. support-team holds no @, STRPOS returns 0, and 0 + 1 is 1, so SUBSTR(email, 1) returns the entire value and the domain column reports support-team as a domain. No error is raised. The guard is NULLIF, which turns the 0 into NULL. NULL + 1 is NULL, SUBSTR with a NULL start is NULL, and a missing separator becomes a missing domain, which is the truthful answer.
STRPOS as a filter
WHERE STRPOS(title, 'Remix') > 0 keeps the same rows as WHERE title LIKE '%Remix%', and both are case-sensitive, so a title holding remix in lower case passes neither until LOWER wraps the column. LIKE reads better when presence is the whole question. STRPOS is the better choice when the query goes on to use where the match was, since the position feeds the SUBSTR that follows.
A separator longer than one character
What does this expression return?
> Each email in listeners needs its domain beside it, and an address that holds no @ must get a NULL domain instead of the whole address. Put the pieces in the order that builds the query.
Joins fail on the space you cannot see
What TRIM leaves behind
- ▸Spaces inside the value stay: TRIM('Low Tide') still holds 2 spaces between the words, and REPLACE('Low Tide', ' ', ' ') closes them
- ▸A character that only looks like a space survives: a non-breaking space pasted from a web page is a different character, and TRIM passes it through
- ▸A value made only of spaces becomes an empty string, which is a different value from NULL; TRIM(NULL) returns NULL
- Trim text columns once, at load time, so every downstream query sees clean values
- Compare LENGTH before and after TRIM to find rows that carry hidden whitespace
- Apply TRIM inside ON when joining to a source you do not control
- Assume a value that prints clean compares clean
- Use TRIM to fix spacing inside a value; that is the job of REPLACE
- Trim one side of a join and leave the other side raw
What does this expression return? The literal has 2 spaces before Low, 2 between the words, and 2 after Tide.
| Expression | Result |
|---|---|
| CONCAT('[', LTRIM(' Low Tide '), ']') | [Low Tide ] |
| CONCAT('[', RTRIM(' Low Tide '), ']') | [ Low Tide] |
| CONCAT('[', TRIM(' Low Tide '), ']') | [Low Tide] |
The GROUP BY on a trimmed expression that folded Low Tide back into one group works on any text column, label names included. Case is a separate fault that TRIM leaves alone: Beatbox Records and beatbox records stay 2 groups after trimming, and LOWER(TRIM(label_name)) folds the stray edges and the case in one expression.
> A label's monthly delivery lands at Spotify as a spreadsheet of tracks and royalties, and the ingest query has to turn it into clean rows before any payout runs.
Cut, find, swap and clean text by position so values that arrive as strings become columns you can trust.
SUBSTR, TRIM, and string surgery 101
- Category
- SQL
- Difficulty
- intermediate
- Duration
- 30 minutes
- Challenges
- 17 hands-on challenges
Topics covered: SUBSTRING(), Edge Extractions, REPLACE(), STRPOS(), TRIM()
Lesson Sections
- SUBSTRING() (concepts: Substrings)
An ISRC is the code that identifies one recording wherever it is sold or streamed. Its 12 characters hold 4 fields laid end to end with no separator between them: a 2-letter country code, a 3-character registrant code for the label, a 2-digit year of registration, and a 5-digit designation. Because nothing marks where one field stops and the next begins, position is the only way to read a field out, and the standard fixes those positions for every ISRC ever issued. Run the query below on 4 track
- Edge Extractions (concepts: 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
- REPLACE() (concepts: String Replacement)
Cleaning numbers that arrived as text The royalty export writes money the way a spreadsheet displays it: $1,250.00. CAST rejects that text, because the dollar sign and the comma have no place in a number. The query strips them with 2 REPLACE calls. Nesting runs from the inside out: the inner call removes the commas, the outer call removes the dollar sign, and CAST receives 1250.00, which DECIMAL(10, 2) accepts. Run it and expand the rows; the amount column now sums. 4 rules that decide the resul
- STRPOS() (concepts: String Search)
The local part gets the same guard. Without it, 0 minus 1 is -1, which SUBSTR treats as a request for no characters and answers with an empty string; with NULLIF the result is NULL, so a later COUNT(local_part) leaves the row out instead of counting an empty value as a name. STRPOS as a filter A separator longer than one character A playlist import arrives with a line per track in the form Artist - Title, with a 3-character separator of space, hyphen, space. STRPOS returns the position of the se
- TRIM() (concepts: Whitespace Trimming)
A string that ends in a space is a different value from the same string without it. Low Tide followed by a space and Low Tide compare as unequal, land in separate GROUP BY groups, and refuse to join. The space is invisible in most output, which is why a report can list Low Tide twice, each row with a partial count, and nobody can see why. TRIM removes whitespace from both ends of a value and leaves the inside alone. LTRIM removes it from the left end only, and RTRIM from the right end only. The