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.
SPLIT(text, ' ')returns an array, an ordered list of the pieces.SPLIT('A3 S LINE', ' ')is the listA3,S,LINE. Text with no separator in it gives a list of one piece, and aNULLtext gives aNULLlist.- To take one piece out of the list, write
[OFFSET(n)]straight after the call:SPLIT('A3 S LINE', ' ')[OFFSET(0)]isA3.OFFSETcounts from 0, so the first piece isOFFSET(0). AnOFFSETpast the end stops the query with an error such as "Array index 1 is out of bounds".[SAFE_OFFSET(n)]returnsNULLthere instead. SUBSTR(text, start, length)returnslengthcharacters from positionstart. Positions count from 1, soSUBSTR('A3 S LINE', 4)isS LINE. Leavelengthout to keep the rest of the text. A negativelengthis an error.STRPOS(text, part)returns the position of the firstpartintext, counted from 1, or 0 whenpartis not there.STRPOS('A3 S LINE', ' ')is 3. So everything before the first space isSUBSTR('A3 S LINE', 1, 2), which isA3.REGEXP_EXTRACT(text, r'pattern')returns the part oftextthat matches. Therbefore the quote makes a raw string, so backslashes reach the pattern as typed. In a pattern,(opens a group and.matches any character. A backslash makes them mean themselves:\(is a bracket and\.is a dot. When the pattern holds one group in parentheses, the function returns what the group matched and uses the rest of the pattern only to find it. The part of the pattern outside the group, such as\(, is matched but left out of the result. Text that does not match givesNULLand no error.- A pattern with two groups is an error, because the function can return only one.
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.modelThe 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.