25 October 2018 · Bozhack-miller ·       Add to Favorites   Report

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 October 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied