如何在BigQuery中用正则验证行程字段并提取出发/到达IATA码

需求说明 #

需要编写正则表达式验证行程字段，从中提取IATA码（3位大写英文字母），并生成departure（出发地）和arrival（目的地）列：

• 行程字段可能包含前缀非IATA内容、多个IATA码连缀的情况
• 若字段中无有效IATA码，两列均返回Nothing

示例输入 #

123424-LYS-OPO-PAR
LYS-OPO
OPO-PAR
OPO-PAR-LYS
LYS-OPO-PAR-...-BKK
12312312-312-231-231

期望输出 #

尝试的代码 #

select
    case
        when regexp_contains(iata.itinerary, r'^\W-|[A-Z]{3}-[A-Z]{3}')
            then split(regexp_substr(iata.itinerary), r'^\W|[A-Z]{3}-[A-Z]{3}'), '-')[offset(0)] 
        else 'Nothing'
    end as departure,
    case
        when regexp_contains(iata.itinerary, r'^\W-|[A-Z]{3}-[A-Z]{3}')
            then ARRAY_REVERSE(split(regexp_substr(upper(iata.itinerary), r'^\W|[A-Z]{3}-[A-Z]{3}'), '-'))[safe_offset(0)]
        else 'Nothing'
    end as arrival 
from iata_data iata

解决方案 #

可以通过REGEXP_EXTRACT_ALL提取所有符合IATA规则的字符串，再基于提取结果数组生成出发和到达列：

WITH extracted_iata AS (
    SELECT
        itinerary,
        -- 提取所有3位大写字母的IATA码
        REGEXP_EXTRACT_ALL(UPPER(itinerary), r'[A-Z]{3}') AS iata_codes
    FROM iata_data
)
SELECT
    CASE WHEN ARRAY_LENGTH(iata_codes) >= 1 THEN iata_codes[OFFSET(0)] ELSE 'Nothing' END AS departure,
    CASE WHEN ARRAY_LENGTH(iata_codes) >= 1 THEN iata_codes[OFFSET(ARRAY_LENGTH(iata_codes)-1)] ELSE 'Nothing' END AS arrival
FROM extracted_iata;

逻辑说明

1. 使用REGEXP_EXTRACT_ALL(UPPER(itinerary), r'[A-Z]{3}')：先把字段转成大写，再提取所有连续3位大写字母的内容，得到IATA码数组
2. 对提取的数组判断长度：
   • 数组长度≥1时，取第一个元素作为departure，最后一个元素作为arrival
   • 数组为空时，两列均返回Nothing

内容的提问来源于stack exchange，提问作者Alex