BigQuery: Syntax error: Unexpected keyword LEFT

2
votes

I got this error of "Syntax error: Unexpected keyword LEFT" from the following SQL (standard SQL) in BigQuery:

select left(cast(ts as string), 16) from temp.loc limit 1;

"ts" is a timestamp field and I wanted to get upto minutes of timestamp. Any idea?

3
google-bigquery
Just running "select left('this is a test', 5)" generats the same error. – kee
This query should be working from what I can see. Maybe include some sample data to your question. – Tim Biegeleisen
@TimBiegeleisen You see my very basic example above in the comment section. This happens from Big Query console. – kee

3 Answers

4
votes

Left isn't a function in Standard SQL. Try using substr instead

SUBSTR

0
votes

If you want to extract the minutes from a timestamp field, use EXTRACT - https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#extract_1

EXTRACT(minutes from ts) as minutes
0
votes

For those looking for how to get the left-most characters in a string: Use LPAD instead of LEFT.

Example:

SELECT 
  LPAD('Hello', 3)

returns 'Hel'