Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 37 ยท Querying and Functions

Strings, Regex and Text Functions in BigQuery

Clean and parse text in BigQuery: CONCAT, SUBSTR, SPLIT, TRIM, REPLACE, REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_REPLACE, FORMAT, collation and common pitfalls, with stand-ins.

Real data is messy: stray spaces, mixed case, emails and URLs that need pulling apart, free-text fields full of codes. BigQuery has a big library of string functions, and a regular-expression engine based on Google's RE2. This lesson goes through the toolbox in the order you usually need it: join, cut, clean, search, extract, replace.

BigQuery syntax is shown read-only, and the portable ideas run on our stand-in database with the SQLite spellings noted.

Joining and measuring

SQL
SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  first_name || ' ' || last_name     AS also_full_name,
  LENGTH(email)                      AS chars,
  UPPER(country)                     AS country_code
FROM `my-project.shop.customers`;

CONCAT returns NULL if any argument is NULL; the || operator does the same. Wrap optional parts in IFNULL(x, ''). LENGTH counts characters, BYTE_LENGTH counts bytes.

SQL Stand-in: build and measure strings ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Cutting text

BigQuery Meaning
SUBSTR(s, pos, len) Substring, 1-based, negative pos counts from the end
LEFT(s, n), RIGHT(s, n) First or last n characters
STRPOS(s, sub) Position of sub, 0 if missing
SPLIT(s, sep) Array of parts
LPAD(s, n, pad), RPAD Pad to a width
REVERSE(s), REPEAT(s, n) Reverse, repeat

SPLIT returns an array, so you take pieces with [OFFSET(n)] (error if out of range) or [SAFE_OFFSET(n)] (NULL if out of range):

SQL
SELECT
  SPLIT(email, '@')[OFFSET(1)]            AS domain,
  SPLIT(path, '/')[SAFE_OFFSET(2)]        AS third_segment
FROM `my-project.shop.customers`;

SQLite has no array SPLIT, but substr and instr can take an email apart:

SQL Stand-in: take an email apart ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Cleaning

  • TRIM(s) removes spaces at both ends; TRIM(s, 'x') removes the listed characters; LTRIM and RTRIM work on one side.
  • LOWER, UPPER, INITCAP change case.
  • REPLACE(s, old, new) replaces literal text.
  • NORMALIZE(s) fixes Unicode forms (accented letters that look the same but differ in bytes).
SQL Stand-in: trim and case ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A standard cleaning pipeline for a messy key, such as joining on emails: LOWER(TRIM(email)). Do it on both sides of the join.

Searching

LIKE with % and _ is available, as is STARTS_WITH(s, prefix), ENDS_WITH(s, suffix) and CONTAINS_SUBSTR(s, text) (case-insensitive). For patterns, use regular expressions:

SQL
SELECT *
FROM `my-project.shop.customers`
WHERE REGEXP_CONTAINS(email, r'^[a-z0-9._%+-]+@example\.com$');

The r'...' raw string prefix means backslashes are not processed twice. Always use it for regexes.

SQL Stand-in: LIKE patterns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Extracting with regular expressions

Function Returns
REGEXP_CONTAINS(s, re) TRUE or FALSE
REGEXP_EXTRACT(s, re) First match, or the first capture group
REGEXP_EXTRACT_ALL(s, re) Array of all matches
REGEXP_REPLACE(s, re, repl) Text with matches replaced
REGEXP_INSTR(s, re) Position of the match
SQL
SELECT
  REGEXP_EXTRACT(url, r'utm_source=([^&]+)')        AS source,
  REGEXP_EXTRACT_ALL(note, r'#(\w+)')               AS hashtags,
  REGEXP_REPLACE(phone, r'[^0-9]', '')              AS digits_only
FROM `my-project.web.visits`;

With one capture group ( ), REGEXP_EXTRACT returns just that group, which is why source gives the value without utm_source=. The engine (RE2) is fast and safe but has no look-behind or back-references.

SQLite has no regex functions in sql.js. You can still practise the extract-the-piece thinking with substr/instr, as below, and in BigQuery switch to REGEXP_EXTRACT:

SQL Stand-in: pull a query parameter out of a URL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The regex version is REGEXP_EXTRACT(url, r'utm_source=([^&]+)'): one line instead of fourteen. That is the practical argument for learning regex.

FORMAT, URLs and encoding

FORMAT builds strings with printf-style placeholders:

SQL
SELECT FORMAT('%s has %d orders (%.1f%% of total)', name, n, pct) AS line
FROM `my-project.shop.summary`;

Other helpers: NET.HOST(url), NET.REG_DOMAIN(url) and NET.PUBLIC_SUFFIX(url) parse URLs properly; TO_BASE64, FROM_BASE64, TO_HEX, MD5, SHA256 handle encodings and hashes (they return BYTES); FARM_FINGERPRINT(s) gives a stable 64-bit hash for bucketing and sampling.

Case sensitivity and collation

String comparison is case-sensitive by default. Options:

  1. Compare LOWER(a) = LOWER(b).
  2. Use COLLATE 'und:ci' on a column or comparison for a case-insensitive Unicode match.
  3. Set a default collation on a dataset or table.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SPLIT('a,b,c', ',') return in BigQuery?

    SPLIT returns an array; index it with OFFSET or SAFE_OFFSET, or flatten with UNNEST.
  2. 2. Why prefix regex patterns with r in BigQuery?

    In a raw string, \d means the regex \d, not a string escape.
  3. 3. REGEXP_EXTRACT with one capture group returns what?

    With a single group, only that part is returned.
  4. 4. What is the result of CONCAT('a', NULL)?

    CONCAT propagates NULL. Use IFNULL to substitute an empty string.

Summary

  • CONCAT, SUBSTR, SPLIT, TRIM, REPLACE and LOWER handle most cleaning.
  • Regex functions (REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_REPLACE) use RE2 and raw strings.
  • SPLIT returns arrays; SAFE_OFFSET avoids errors.
  • Comparisons are case-sensitive unless you lower-case or use collation.
  • NULL in CONCAT gives NULL.

Next: Window Functions and QUALIFY