MySQL正则提取字符串中参考编号：仅能提取末尾值的问题求助

Got it, let's break down why your current query isn't working first, then fix this properly.

Your existing SUBSTRING_INDEX approach relies on the reference number being at the end of the Description field—you're splitting on spaces and grabbing the last segment. But when the number is somewhere in the middle, this logic falls apart because it doesn't actually match the pattern of your reference number, just splits on a delimiter.

The Fix: Use REGEXP_SUBSTR (MySQL 8.0+) #

MySQL 8.0 and later includes the REGEXP_SUBSTR function, which is built specifically to extract substrings that match a regular expression pattern. This is perfect for your use case, since it doesn't care where the reference number is in the string—it just looks for the pattern you define.

Here's the updated query:

SELECT 
  Description,
  REGEXP_SUBSTR(Description, '[[:upper:]]{1,2}[[:digit:]]+[[:upper:]]') AS Reference
FROM db_test.tbl_regex;

Let's break down the regex pattern:

• [[:upper:]]{1,2}: Matches 1 or 2 uppercase letters (covers both single-letter and double-letter starts, aligned with your original regex)
• [[:digit:]]+: Matches one or more digits (handles any length of numbers in the middle)
• [[:upper:]]: Matches exactly one uppercase letter at the end

This query will pull the first occurrence of your reference number pattern from anywhere in the Description string—whether it's at the start, middle, or end.

What if you're using an older MySQL version (pre-8.0)? #

If you can't upgrade, you'll need a workaround since older versions don't support REGEXP_SUBSTR. One method combines SUBSTRING with regex position checks:

SELECT 
  Description,
  SUBSTRING(
    Description,
    -- Get the starting position of the pattern
    REGEXP_INSTR(Description, '[[:upper:]]{1,2}[[:digit:]]+[[:upper:]]'),
    -- Calculate the length of the matched segment
    LENGTH(REGEXP_SUBSTR(Description, '[[:upper:]]{1,2}[[:digit:]]+[[:upper:]]'))
  ) AS Reference
FROM db_test.tbl_regex;

Note: This older method is less reliable, especially if there are spaces immediately after the reference number. Upgrading to MySQL 8.0 is strongly recommended for cleaner, more maintainable regex handling.

Example Test Case #

If your Description value is:

"Sample text with reference AB12345678Z in the middle and another XY98765W at the end"

The REGEXP_SUBSTR query will return AB12345678Z as the Reference value—exactly the middle reference you need.

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