Home > Mobile >  What is the equivalent of MySQL LOCATE to Bigquery?
What is the equivalent of MySQL LOCATE to Bigquery?

Time:10-27

So I have this query with LOCATE function:

SELECT TRIM(CASE WHEN store_name like "%|%" THEN LEFT(store_name, LOCATE('|', store_name) - 1) ELSE
                CASE WHEN store_name like "%,%" THEN LEFT(store_name, LOCATE(',', store_name) - 1) ELSE
                     CASE WHEN store_name like "% - %" THEN LEFT(store_name, LOCATE(' - ', store_name) - 1) ELSE
                         store_name
                     END
                END
             END)

Everything is working, but I need to change from MySQL to Bigquery now. When I tried to paste this query in Bigquery editor, I got an error: Function not found: LOCATE at [3:76]

CodePudding user response:

There are different similar functions to Locate using BigQuery such as REGEXP_EXTRACT[1], as the INSTR [2], or SUBSTR[3] function.

[1]enter image description here

  • Related