String Functions: Intermediate

Every character in a string sits at a numbered position, and the numbering starts at 1. SUBSTR(text, start, length) returns length characters beginning at position start. SUBSTRING is also written SUBSTR, the spelling this lesson uses throughout. It hands back text, a VARCHAR, even when the characters it cuts out are digits.

The year is 4 characters from position 1, the month is 2 characters from position 6, and the day is 2 characters from position 9. The third argument is a count of characters, so SUBSTR('2019-11-29', 6, 2) returns 11 while SUBSTR('2019-11-29', 6, 7) returns 11-29: positions 6 through 10 are all the value has, and the slice stops at the last of them.

Both output columns hold text: 2019 and 11 are strings of digits. Comparing release_month to '11' works, but sorting months or adding to a year needs a number, so wrap the slice: CAST(SUBSTR(release_date, 6, 2) AS INTEGER) returns 11. Use TRY_CAST when some rows might hold something other than digits at those positions.

Leave out the length and SUBSTR keeps everything from start to the last character, which is what play logs need. In spotify:track:6rqhFgbbKwnb9MLmUQDhG6 the prefix spotify:track: fills positions 1 to 14, so SUBSTR(item_uri, 15) returns the 22-character ID after it, and the query never needs to know how long the ID is.

SUBSTR raises no error when a position falls outside the value. It returns whatever characters the requested slice overlaps, and an empty string when it overlaps none. Here is how that plays out on the 8-character value playlist:

An empty string is a value of its own, distinct from NULL. Blank slices pass an IS NOT NULL check and gather into a group of their own under GROUP BY, so a column full of blanks is a sign that the layout you assumed doesn't match the data.

Positions count characters, matching LENGTH: the é in café fills 1 position, so SUBSTR('café', 4, 1) returns é.

The same counting finds a piece in the middle of a code with a published layout. An ISRC, the 12-character code that identifies a recording, is 2 letters of country, then 3 characters of registrant, then 2 digits of year, then 5 digits of designation. A piece starts 1 position after the total width of the pieces before it, and its length is its own width. In USRC17607839 the year follows 2 + 3 characters, so it starts at position 6 and runs for 2: SUBSTR(isrc, 6, 2) returns 76.

Plenty of the slices you need sit against one edge of the value: a country code at the end of a locale, a file extension, the last 4 digits of an account number. The start edge is position 1 for every value, so SUBSTR(locale, 1, 2) reads the first 2 characters whatever comes after them.

For the end edge, give SUBSTR a negative start. Position -1 is the last character and -2 the one before it, so SUBSTR(locale, -2) returns the final 2 characters of a value of any length. Leaving out the length still means read to the end.

Both rows of numbers point at the same characters, so SUBSTR('fil_PH', 5) and SUBSTR('fil_PH', -2) both return PH. The negative form keeps pointing at the last 2 characters when the next value is 1 character longer or shorter, which the positive form can't do.

Look at the fil_PH row in either tab. Its country code is right, because the country always fills the last 2 positions. Its language code comes back as fi: Filipino has no 2-letter language code, so this locale opens with fil, and a 2-character cut from the start splits it. An edge slice is trustworthy only on the side where the fixed-width piece sits. The other side needs the position of the separator, which STRPOS supplies 2 sections from now.

The same reasoning rescues the play log. A track prefix is 14 characters and an episode prefix is 16, but in this log every ID is 22 characters at the very end, so SUBSTR(item_uri, -22) returns the ID for both kinds of item.

The end edge holds only while every ID keeps that width. A 21-character ID would come back with the colon in front of it, and no error would flag the row.

The 2 edges disagree when a value is shorter than the slice. From the start, SUBSTR('42', 1, 4) returns 42, the characters that exist. From the end, SUBSTR('42', -4) returns an empty string: position -4 lies before the first character, and a start outside the value matches nothing.

A blank last_four on a payout statement looks like a display glitch, so make short values visible instead. CASE WHEN LENGTH(account_ref) >= 4 THEN SUBSTR(account_ref, -4) END returns NULL for them, and a NULL shows up in every check that counts missing values.

An edge slice works in WHERE as well as in SELECT. WHERE SUBSTR(locale, 1, 2) = 'en' keeps rows whose first 2 characters are en, and the SELECT list can then label each kept row by its last 2. A start of 0 is a common slip: it points at no character, so SUBSTR(locale, 0, 2) returns an empty string that never equals 'en'. Dropping the minus sign is the other, and it changes which edge you read from: SUBSTR('en_GB', 2) starts at the second character and returns n_GB.

REPLACE(text, search, replacement) finds every occurrence of search and puts replacement in its place. With only 2 arguments it deletes each match. Where SUBSTR cuts by position, REPLACE works by content: you name the characters, and it finds them wherever they sit.

Finance exports format money for people to read. $12,400.75 won't CAST to DECIMAL, because $ and , are not digits, and TRY_CAST quietly turns it into NULL. Delete those 2 symbols and the text that remains is one the cast accepts.

Read nested calls from the inside out. The inner REPLACE deletes every comma, the outer one deletes the dollar sign from that result, and CAST receives 12400.75. Each call takes the string the previous call produced, so a chain of REPLACE calls is a small pipeline whose first step is the innermost.

Because each step sees the output of the step before it, 2 swaps that touch the same character can collide. Many European exports write 1.250,00 for the amount 1250: dots group the thousands and the comma marks the decimals. Converting it means deleting the dots and turning the comma into a dot, and only 1 order of those 2 steps works.

REPLACE can't tell a match you meant from one you did not. REPLACE('carla.combs@mail.com', '.com') deletes both occurrences of .com and returns carlabs@mail. It's case-sensitive as well: REPLACE('Hip-Hop', 'hip-hop', 'hip hop') leaves the value unchanged, because H and h are different characters.

The first tab returns 6 groups for what listeners hear as 2 genres. The normalized tab folds them into hip hop with 5 plays and indie pop with 2. GROUP BY repeats the whole expression because the genre alias is assigned in SELECT, which runs after grouping.

Choosing a swap for grouping starts with 2 spellings that ought to land together. Lowercase both and find the characters where they still differ: lo-fi and lo_fi differ only at position 3. Pick one form as the target and replace the other toward it, so REPLACE(LOWER(genre_tag), '_', '-') sends both to lo-fi. Replacing toward a character that already means something else in the column would merge tags you meant to keep apart.

STRPOS(text, search) returns the position where search first appears in text, counting from 1, and 0 when it does not appear. On its own it answers where. Passed into SUBSTR as a start or a length, it lets every slice follow each value's own layout.

Email addresses show the pattern. In ana@beatbox.io the @ is at position 4, so the domain begins 1 position later, at 5. Written with STRPOS, that becomes SUBSTR(email, STRPOS(email, '@') + 1), which works for an address of any length.

Study the last row. support-team has no @, so STRPOS returns 0. The domain slice starts at 0 + 1, which is position 1, and returns the whole value, so a count of listeners by domain would report a domain named support-team. The username slice, SUBSTR(email, 1, STRPOS(email, '@') - 1), fails the other way: its length becomes -1 and it returns an empty string.

Turn that 0 into NULL with NULLIF and each slice built on it becomes NULL as well, since NULL plus 1 is NULL and SUBSTR given a NULL start returns NULL. Malformed rows then show up as missing values that COUNT(domain) skips, and no fake domain reaches the report.

A play URI holds 2 colons, and STRPOS stops at the first one, position 8. To reach the second, search only the text after the first: SUBSTR(item_uri, 9) begins right after it, STRPOS finds the colon in that remainder, and adding 8 turns the remainder's position back into a position in the full URI, 14 in a track URI and 16 in an episode URI. STRPOS(item_uri, ':', 2) is a shorthand that asks for the second occurrence directly and returns the same numbers. The ID starts 1 position after that colon, and the item type fills the gap between position 9 and it, so the type's length is the colon's position minus 9. The CTE below finds the colon once and slices with it twice.

No slice in that query assumes a width. A playlist URI, or an ID of a different length, would still split cleanly. The fi bug from the locale column has the same cure: SUBSTR(locale, 1, STRPOS(locale, '_') - 1) returns fil for fil_PH and en for en_US.

Every delimiter slice comes down to 1 rule: the delimiter's own position is the boundary, and neither piece includes it. The piece before it ends 1 position earlier, so its length is the delimiter's position minus 1. The piece after it starts 1 position later, at the position plus 1. In fil_PH the delimiter sits at 4, so the language is 3 characters from position 1 and the country starts at 5.

TRIM(text) removes whitespace from both ends of a value. LTRIM removes it from the left end only, RTRIM from the right end only, and none of the 3 touches spaces between words.

Edge whitespace is invisible in a result grid and decisive in a comparison. 'Low Tide ' and 'Low Tide' differ by 1 character, so they fail =, land in separate GROUP BY groups and refuse to pair in a JOIN. LENGTH shows what the grid hides, and wrapping the value in brackets with CONCAT shows where it starts and ends.

TRIM turns 3 spellings of Low Tide into the same 8 characters. Paper Moons keeps its double space at 12 characters, because the extra space sits between words. REPLACE(artist_name, ' ', ' ') shrinks each pair of spaces to 1 in a single pass, so a run of 3 spaces needs a second pass.

In the raw tab only Paper Moons gets an artist_id. The other 2 names read identically to the artist table's, but Low Tide ends in a space and Night Drive starts with one. Trimming inside the ON clause matches all 3. With an INNER JOIN the 2 unmatched artists would have dropped out of the total, leaving no NULL behind to hint that anything was missing.

A leading space pushes every position 1 to the right. SUBSTR(' en_US', 1, 2) returns a space followed by e, and STRPOS(' en_US', '_') returns 4 where the clean value gives 3. Trim first, then hand the clean value to SUBSTR, STRPOS or REPLACE.

The fix in that case follows a fixed order. TRIM goes innermost, wrapped around the column at every place the column appears, because SUBSTR and STRPOS both read positions and each needs the cleaned value. For an email column with stray spaces, SUBSTR(TRIM(email), STRPOS(TRIM(email), '@') + 1) returns the domain whether or not the row was padded.