Finding the location of one string within another string in Bigquery

Question

I couldn't find a function in BigQuery query reference which looks for one string within a second one and returns the index of the location. Something like instr() in other SQL dialects. Is there any substitute or any technique to achieve this?

For example: Looking into "de" in "abcdef" will return 4.

Answer 1

One way you can do this is with a Regular Expression extract (see reference here):

SELECT
  title, LENGTH(REGEXP_EXTRACT(title, r'^(.*)def.*')) + 1 AS location_of_fragment
FROM
  [publicdata:samples.wikipedia]
WHERE
  REGEXP_MATCH(title, r'^(.*)def.*')
LIMIT 10;

Returns:

Row title   location_of_fragment     
1   Austrian air defense    14   
2   Talk:Interface defeat   16   
3   High-definition television  6    
4   Talk:IAU definition of planet   10   
5   Wikipedia:Articles for deletion/Culture defines politics    41   
6   Wikipedia:WikiProject Spam/LinkReports/defenders.org    40   
7   Adenine phosphoribosyltransferase deficiency    35   
8   Stay-at-home defenceman 14   
9   Manganese deficiency (plant)    11   
10  High-definition television  6