SUBSTRING()

Concepts covered: Substrings

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.

Write the positions over a value once and every slice turns into a pair of numbers you can read off. Here is a release date that was loaded as text:

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.

Run those slices on a table of releases. Every row gets the same cut, and that is safe here because every date in the column was written in the same layout.

SELECT
  track_name,
  SUBSTR(release_date, 1, 4) AS release_year,
  SUBSTR(release_date, 6, 2) AS release_month
FROM releases

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.

Reading to the end of the value

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.

SELECT
  play_id,
  SUBSTR(item_uri, 15) AS item_id
FROM play_events

Positions outside the value

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:

Run 3 of those calls yourself. Wrapping a slice in LENGTH turns an empty result into a 0 you can see in the grid.

SELECT
  SUBSTR(word, 5, 100) AS past_end,
  LENGTH(SUBSTR(word, 9)) AS start_past_end,
  LENGTH(SUBSTR(word, 0, 3)) AS start_zero
FROM words

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 é.

Every slice in this section relied on positions that hold still. Before hard-coding a number, decide which kind of column you are cutting.

Positions you can hard-code

• Dates loaded in one layout, such as 2019-11-29
• Codes whose width is fixed by a published spec
• A prefix that every row in the column shares

Positions that move

• Names and addresses typed by people
• IDs whose prefix depends on the kind of item
• Columns merged from sources with different layouts

A podcast play is logged in the same table as spotify:episode:512ojhOuo1ktJprKbVcKyQ. What does SUBSTR(item_uri, 15) return for it?

• 512ojhOuo1ktJprKbVcKyQ, because SUBSTR finds where the ID begins
• e:512ojhOuo1ktJprKbVcKyQ, because position 15 is the last letter of episode
• :512ojhOuo1ktJprKbVcKyQ, because the longer prefix shifts the ID by 1
• An empty string, because the value runs past position 36

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.

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.