STRPOS()

Concepts covered: String Search

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.

SELECT
  email,
  STRPOS(email, '@') AS at_pos,
  SUBSTR(email, STRPOS(email, '@') + 1) AS domain
FROM listeners

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.

Guarding the not-found case

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.

SELECT
  email,
  SUBSTR(email, 1, NULLIF(STRPOS(email, '@'), 0) - 1) AS username,
  SUBSTR(email, NULLIF(STRPOS(email, '@'), 0) + 1) AS domain
FROM listeners

The second colon in a play URI

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.

WITH located AS (
  SELECT
    item_uri,
    STRPOS(SUBSTR(item_uri, 9), ':') + 8 AS second_colon
  FROM play_events
)
SELECT
  item_uri,
  second_colon,
  SUBSTR(item_uri, 9, second_colon - 9) AS item_type,
  SUBSTR(item_uri, second_colon + 1) AS item_id
FROM located

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.

What does STRPOS('spotify:playlist:37i9dQZF1DXcBWIGoYBM5M', ':', 2) return?

• 8, the position of the first colon
• 16, the last letter of playlist
• 17, the colon after playlist
• 0, because only tracks and episodes have 2 colons

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.

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.