How to Use SUBSTR (Substring) in BigQuery

There is no SUBSTRING function in GoogleSQL, only SUBSTR. Omit the length argument to take everything from the position onward. Asking for more characters than the string holds returns what is there rather than erroring.

SELECT SUBSTR('SaturnSQL', 1, 6) AS first_six,   -- Saturn
       SUBSTR('SaturnSQL', 7)    AS rest;        -- SQL

Counting from the end

A negative position is relative to the end of the string, so SUBSTR(value, -3) is the last three characters. LEFT and RIGHT exist too and read better when you just want a fixed number of characters from one end.

SELECT SUBSTR(order_ref, -4) AS last_four,
       RIGHT(order_ref, 4)    AS also_last_four,
       LEFT(order_ref, 3)     AS prefix
FROM orders;

Cutting at a delimiter

SUBSTR takes fixed offsets, so pair it with STRPOS when the cut point varies. For anything repeated, SPLIT is clearer than nesting STRPOS calls.

SELECT SUBSTR(email, 1, STRPOS(email, '@') - 1) AS local_part,
       SUBSTR(email, STRPOS(email, '@') + 1)    AS domain
FROM users;

Bytes versus characters

On a STRING, positions count Unicode characters, so multi-byte text behaves the way you would expect. On BYTES, the same function counts bytes, which will cut a multi-byte character in half. Convert with SAFE_CONVERT_BYTES_TO_STRING first if the input is BYTES.

Do more with BigQuery

• Export BigQuery to Google Sheets on a schedule
• Send BigQuery results to Slack
• BigQuery SQL client in the browser