BigQuery Syntax error: Unexpected keyword LEFT or RIGHT function

Problem 🔍

LEFT() and RIGHT() Functions are not supported in Bigquery. If your SQL query has these functions, Bigquery will throw the below error message:

Syntax error: Unexpected keyword LEFT at [9:12]

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

Solution 💡

LEFT Function equivalent in Bigquery

SUBSTR is the equivalent of LEFT function. Rewrite the below query,

select LEFT(Name, 16) from table;

to

select SUBSTR(Name,1, 16) from table;

RIGHT Function equivalent in Bigquery

SUBSTR is the equivalent of RIGHT function. Rewrite the below query,

select RIGHT(Name, 16) from table;

to

select SUBSTR(Name,LENGTH(Name)-15, 16) from table;

Substitute the unsupported functions with SUBSTR and it should fix the issue

mCollins

posted on 18 Feb 19