25 Oct 18 · Bozhack-miller · #bigquery ·   Bookmark   ×

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:

UPDATE dataset.dataset
SET New_column = RIGHT(link_id, LEN(link_id) - 3)

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


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


posted on 25 Oct 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds