如何在SnowFlake中从含多括号的字符串提取卡号

当字符串包含多对括号时，原方法会匹配到第一个左括号，导致提取的内容不是目标卡号。解决的核心是锁定卡号的前置标识（"Card number"），只提取该标识后方括号内的内容。

方案1：基于字符串定位函数（通用SQL） #

如果你的数据库不支持正则，可以通过嵌套POSITION和SUBSTRING实现精准定位：

SELECT 
  'A user entered through a door (using an anti-pass) and his Card number (123456) other text' AS card_number_text,
  SUBSTRING(
    card_number_text,
    -- 定位到"Card number ("之后的起始位置
    POSITION('Card number (' IN card_number_text) + LENGTH('Card number ('),
    -- 计算从起始位置到第一个右括号的长度
    POSITION(')' IN SUBSTRING(card_number_text, POSITION('Card number (' IN card_number_text) + LENGTH('Card number ('))) - 1
  ) AS extracted_card_number;

逻辑说明：

1. 先用POSITION('Card number (' ...)找到目标左括号的起始位置，加上标识文本长度得到卡号的第一个字符位置。
2. 从该位置截取子串后，再找第一个右括号的位置，以此确定卡号的长度。

方案2：正则表达式（支持正则的数据库） #

如果数据库支持正则函数（如PostgreSQL、MySQL 8+、Oracle），用正则会更简洁直观：

SELECT 
  'A user entered through a door (using an anti-pass) and his Card number (123456) other text' AS card_number_text,
  -- 捕获"Card number ("和")"之间的数字序列
  REGEXP_SUBSTR(card_number_text, 'Card number \\((\\d+)\\)', 1, 1, 'i', 1) AS extracted_card_number;

正则说明：

• Card number \\(：匹配卡号标识后的左括号（转义符\\适配SQL语法）
• (\\d+)：捕获1个及以上数字（即卡号内容）
• 最后一个参数1：指定返回第一个捕获组的内容（也就是括号里的数字）
• 'i'：可选，开启不区分大小写匹配（如果需要忽略"Card Number"的大小写差异）

注意事项 #

• 如果卡号可能包含非数字字符，把正则中的\\d+替换为[^)]+（匹配除右括号外的所有字符）即可。
• 不同数据库的正则函数语法略有差异，比如SQL Server使用REGEXP_SUBSTRING，Oracle的REGEXP_SUBSTR参数顺序不同，需根据实际数据库调整。

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