正则表达式逗号分隔定界符

efe 发表于 Dev

埃菲

我正在尝试用逗号分隔符来分隔我的专栏。因此，该列具有多个值，例如；139,239,338,323。出于某种原因，以下代码可用于第一列，但其余列为空。

SELECT  
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){0}([^,\/]*),\/?') as Word0,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){1}([^,\/]*),\/?') as Word1,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){2}([^,\/]*),\/?') as Word2,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){3}([^,\/]*),\/?') as Word3,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){4}([^,\/]*),\/?') as Word4,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){5}([^,\/]*),\/?') as Word5,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){6}([^,\/]*),\/?') as Word6,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){7}([^,\/]*),\/?') as Word7,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){8}([^,\/]*),\/?') as Word8,
Regexp_extract(StringToParse,r'^(?:[^,\/]*,\/){9}([^,\/]*),\/?') as Word9
FROM
(SELECT event_list AS StringToParse FROM `mytable.2017`)

米哈伊尔·伯利安（Mikhail Berlyant）

尝试下面的BigQuery标准SQL

#standardSQL
SELECT 
  SPLIT(StringToParse)[SAFE_OFFSET (0)] AS Word0, 
  SPLIT(StringToParse)[SAFE_OFFSET (1)] AS Word1, 
  SPLIT(StringToParse)[SAFE_OFFSET (2)] AS Word2, 
  SPLIT(StringToParse)[SAFE_OFFSET (3)] AS Word3, 
  SPLIT(StringToParse)[SAFE_OFFSET (4)] AS Word4, 
  SPLIT(StringToParse)[SAFE_OFFSET (5)] AS Word5, 
  SPLIT(StringToParse)[SAFE_OFFSET (6)] AS Word6, 
  SPLIT(StringToParse)[SAFE_OFFSET (7)] AS Word7, 
  SPLIT(StringToParse)[SAFE_OFFSET (8)] AS Word8, 
  SPLIT(StringToParse)[SAFE_OFFSET (9)] AS Word9 
FROM 
  (SELECT event_list AS StringToParse FROM `mytable.2017`)

您可以使用以下虚拟数据来测试/玩游戏

#standardSQL
WITH `mytable.2017` AS (
  SELECT '139,239,338,323' AS event_list UNION ALL
  SELECT '123,456,789,135'
)
SELECT 
  SPLIT(StringToParse)[SAFE_OFFSET (0)] AS Word0, 
  SPLIT(StringToParse)[SAFE_OFFSET (1)] AS Word1, 
  SPLIT(StringToParse)[SAFE_OFFSET (2)] AS Word2, 
  SPLIT(StringToParse)[SAFE_OFFSET (3)] AS Word3, 
  SPLIT(StringToParse)[SAFE_OFFSET (4)] AS Word4, 
  SPLIT(StringToParse)[SAFE_OFFSET (5)] AS Word5, 
  SPLIT(StringToParse)[SAFE_OFFSET (6)] AS Word6, 
  SPLIT(StringToParse)[SAFE_OFFSET (7)] AS Word7, 
  SPLIT(StringToParse)[SAFE_OFFSET (8)] AS Word8, 
  SPLIT(StringToParse)[SAFE_OFFSET (9)] AS Word9 
FROM 
  (SELECT event_list AS StringToParse FROM `mytable.2017`)

同时，如果由于某种原因您必须在此查询中使用正则表达式-请尝试以下

#standardSQL
SELECT  
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(0)]  AS Word0,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(1)]  AS Word1,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(2)]  AS Word2,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(3)]  AS Word3,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(4)]  AS Word4,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(5)]  AS Word5,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(6)]  AS Word6,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(7)]  AS Word7,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(8)]  AS Word8,
  REGEXP_EXTRACT_ALL(StringToParse, r'([^,\/]*),\/?')[SAFE_OFFSET(9)]  AS Word9
FROM
  (SELECT event_list AS StringToParse FROM `mytable.2017`)

当然，在以上所有示例中，您都可以通过引入REGEXP_EXTRACT_ALL的SPLIT子查询来简化代码，然后在外部select中选择每个数组的元素

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。