google-bigquery - 在 Bigquery 中查找一个字符串在另一个字符串中的位置

Question

我在 BigQuery 查询引用中找不到一个函数，它在第二个字符串中查找一个字符串并返回该位置的索引。类似于其他 SQL 方言中的 instr() 。有没有替代品或任何技术来实现这一目标？

例如:查看“abcdef”中的“de”将返回4。

Answer 1

一种方法是使用正则表达式提取 ( 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;

返回:

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