Pull text apart: SPLIT, SUBSTR, REGEXP_EXTRACT

One text value often holds several facts, such as a model name with its trim level or a defect description with a rule number in brackets. This lesson takes such values apart, using dim_vehicle (one row per car, with the model name in spec.model) and fact_defect (one row per defect found in a test, described in defect_text).

How it works

Three functions cut text in three ways. SPLIT cuts at a separator you name. SUBSTR cuts at a position, and STRPOS finds that position. REGEXP_EXTRACT returns the part that matches a pattern. They differ most when the piece you want is missing from a value: one of them returns NULL, one returns a position of 0, and one gives a list that is too short.

Common mistake

SUBSTR(model, 1, 2) gives the right first word for A3, A4 and Q5 and raises no error, so it looks finished. Then E-TRON becomes E- and RS4 becomes RS. Fixed numbers work only while every value has the same layout. Find the cut with STRPOS, SPLIT or a pattern, and look at the values that are shaped differently.

Worked example

SELECT DISTINCT spec.model,
  SPLIT(spec.model, ' ')[OFFSET(0)] AS first_word,
  SUBSTR(spec.model, STRPOS(spec.model, ' ') + 1) AS after_first_word,
  REGEXP_EXTRACT(spec.model, r'([0-9]+) TFSI') AS tfsi_number
FROM `learn.mot.dim_vehicle`
WHERE spec.model IN ('A4 S LINE 35 TFSI S-A', 'TT')
ORDER BY spec.model

The first model has six words. OFFSET(0) takes A4. STRPOS finds the first space at position 3, so SUBSTR from position 4 returns the rest. The pattern finds the digits before TFSI and returns only the group, 35.

The model TT is one word. SPLIT still returns TT. STRPOS returns 0, so SUBSTR starts at position 1 and returns all of TT. The pattern does not match, so tfsi_number is NULL.

All BigQuery lessons