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\nUPDATE dataset.dataset\nSET New_column = RIGHT(link_id, LEN(link_id) - 3)\nWHERE TRUE\n\n\nError: 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)\n\n--Result: copy

To get the right 4 characters:

select substr('copycoding',length('copycoding')-3,4)\n
--Result: ding

Bozhack-miller

posted on