PostgreSQL regexp_matches正则匹配不符合预期问题排查

Let's break down exactly what's happening here and how to fix it:

Key Issues Explained #

1. PostgreSQL's regexp_matches Returns Capture Groups, Not the Full Match #

Unlike some regex testing tools that default to showing the full matched string, PostgreSQL's regexp_matches function returns only the contents of your capture groups (the parts wrapped in ()). If your regex has multiple capture groups, it returns an array where each element corresponds to a capture group's value. Using unnest() then splits that array into individual rows.

2. Repeated Capture Groups Only Keep the Last Match (POSIX Standard) #

Your regex uses repeated capture groups like ([\s\-\/\.]*?0){3}. In PostgreSQL's POSIX-compliant regex engine, when you repeat a capture group (with {n} or other quantifiers), only the last occurrence of that capture group is retained.

For example, in the string 4-0001-1234:

• The first capture group ([\s\-\/\.]*?0){3} matches three instances of "0" (with optional separators), but only the last match (0) is stored.
• The second capture group ([\s\-\/\.]*?[12]){1} matches the 1 in 0001, so it stores 1.
• The third capture group ([\s\-\/\.]*?\d){4} matches four digits, but only the last one (4) is stored.

That's why you get 0, 1, 4 as results.

Fixes Based on Your Goal #

If You Want the Full Matched String #

To get the entire string that fits your pattern, convert all your capture groups to non-capturing groups using (?:...). Non-capturing groups define a subpattern without storing their contents, so regexp_matches will return the full match instead of individual group values.

Adjusted regex:

4(?:[\s\-\/\.]*?0){3}(?:[\s\-\/\.]*?[12]){1}(?:[\s\-\/\.]*?\d){4}

Updated SQL query:

SELECT unnest(regexp_matches('4-0001-1234', '4(?:[\s\-\/\.]*?0){3}(?:[\s\-\/\.]*?[12]){1}(?:[\s\-\/\.]*?\d){4}', 'g'));

This will return the full matched string: 4-0001-1234.

If You Need to Extract Specific Subparts #

If you want to capture individual components (like the three 0s, the 1/2, and the four digits), you can't rely on repeating a single capture group. Instead, define separate non-repeated capture groups for each part you want to extract. For example:

4(?:[\s\-\/\.]*?0)(?:[\s\-\/\.]*?0)(?:[\s\-\/\.]*?0)(?:[\s\-\/\.]*?([12]))(?:[\s\-\/\.]*?(\d))(?:[\s\-\/\.]*?(\d))(?:[\s\-\/\.]*?(\d))(?:[\s\-\/\.]*?(\d))

This will capture the 1 and each of the four digits as separate group values, which regexp_matches will return in an array.

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