Using LEFT() or RIGHT() in standard SQL in BigQuery

How to get a few characters from left using left() function. While running the following code in standard SQL in BigQuery, you will get an error:

#standardSQL
UPDATE dataset.dataset
SET New_column = RIGHT(link_id, LEN(link_id) - 3)
WHERE TRUE


Error: Syntax error: Unexpected keyword RIGHT at [8:18]

Solution:

In BigQuery there is no LEFT() or RIGHT() functions, You can use substr() instead :

To get the left 4 characters:

select substr('copycoding',1,4)

--Result: copy

To get the right 4 characters:

select substr('copycoding',length('copycoding')-3,4)

--Result: ding

Bozhack-miller

posted on 25 Oct 18