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.