SQL中使用REGEX_EXTRACT提取括号内特定模式的方法咨询

Alright, let's break down how to solve your regex extraction issue. Your current formula only grabs the first bracketed string because REGEX_EXTRACT (in most SQL engines) returns just the first match by default, plus it doesn't restrict the content inside the brackets to your specific [xx-XX] pattern. Here's how to fix both problems:

Step 1: Refine the Regex to Target Your Exact Pattern #

First, update your regular expression to only match brackets containing two letters, a hyphen, then two more letters (any case, since you said x represents any English letter). The refined regex is:

r'\[([A-Za-z]{2}-[A-Za-z]{2})\]'

Let's break this down:

• \[ / \]: Escape the literal square brackets (since they're special characters in regex)
• ([A-Za-z]{2}-[A-Za-z]{2}): A capture group that matches:
  • [A-Za-z]{2}: Exactly 2 uppercase or lowercase English letters
  • -: The literal hyphen
  • [A-Za-z]{2}: Another 2 letters

Step 2: Extract All Matches (Not Just the First) #

The approach here depends on which SQL engine you're using—different tools have different functions for global regex matching:

For BigQuery #

Use REGEX_EXTRACT_ALL instead of REGEX_EXTRACT to get all matching patterns as an array:

SELECT REGEX_EXTRACT_ALL(your_column_name, r'\[([A-Za-z]{2}-[A-Za-z]{2})\]') AS extracted_patterns
FROM your_table;

Example output for a cell like Hello [ab-CD] world [xy-ZW] test:
['ab-CD', 'xy-ZW']

For PostgreSQL #

Use regexp_matches with the g (global) flag to return all matches. This returns each match as a row; wrap it in array_agg if you want results in a single array:

-- Return each match as a separate row
SELECT regexp_matches(your_column_name, '\[([A-Za-z]{2}-[A-Za-z]{2})\]', 'g') AS extracted_patterns
FROM your_table;

-- Return all matches as a single array per row
SELECT array_agg(match) AS extracted_patterns
FROM your_table, regexp_matches(your_column_name, '\[([A-Za-z]{2}-[A-Za-z]{2})\]', 'g') AS match;

For MySQL 8.0+ #

MySQL doesn't have a built-in global extract function, but you can use a recursive CTE to pull all matches, or use REGEXP_REPLACE to isolate matches then split them:

-- Option 1: Recursive CTE to extract all matches
WITH RECURSIVE extract_matches AS (
  SELECT 
    your_column_name AS original_text,
    REGEXP_SUBSTR(your_column_name, '\[([A-Za-z]{2}-[A-Za-z]{2})\]') AS matched,
    1 AS match_number
  FROM your_table
  WHERE REGEXP_SUBSTR(your_column_name, '\[([A-Za-z]{2}-[A-Za-z]{2})\]') IS NOT NULL
  UNION ALL
  SELECT
    original_text,
    REGEXP_SUBSTR(original_text, '\[([A-Za-z]{2}-[A-Za-z]{2})\]', 1, match_number + 1),
    match_number + 1
  FROM extract_matches
  WHERE REGEXP_SUBSTR(original_text, '\[([A-Za-z]{2}-[A-Za-z]{2})\]', 1, match_number + 1) IS NOT NULL
)
SELECT original_text, GROUP_CONCAT(matched) AS extracted_patterns
FROM extract_matches
GROUP BY original_text;

If You Only Need the First Valid [xx-XX] Pattern #

If you don't need all matches, just the first one that fits your [xx-XX] format, use your original REGEX_EXTRACT with the refined regex:

SELECT REGEX_EXTRACT(your_column_name, r'\[([A-Za-z]{2}-[A-Za-z]{2})\]') AS first_valid_pattern
FROM your_table;

This will skip any bracketed content that doesn't match xx-XX and grab the first one that does.

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