MySQL低版本（<8.0）如何获取两特定字符串间首次出现的整数值

Since MySQL versions older than 8.0 don't support REGEXP_SUBSTR, we need to combine basic string functions to target the first occurrence of ABC[digits]_ and extract the numeric value in between. The original substring_index approach fails when "ABC" appears multiple times because it grabs the last occurrence—here's how to fix that:

Step-by-Step Workaround #

We'll use LOCATE to find positions of the first "ABC" and the first "_" that comes right after it, then use SUBSTRING to pull out the digits. We'll also add safeguards to handle edge cases (like no matches or non-numeric values between ABC and _).

Example Query #

SELECT 
  CASE
    -- Check if ABC exists, and there's an underscore after it
    WHEN LOCATE('ABC', input_str) > 0 
         AND LOCATE('_', input_str, LOCATE('ABC', input_str) + 3) > LOCATE('ABC', input_str) + 3
         -- Ensure the extracted value is only digits
         AND SUBSTRING(input_str, LOCATE('ABC', input_str) + 3, LOCATE('_', input_str, LOCATE('ABC', input_str) + 3) - (LOCATE('ABC', input_str) + 3)) REGEXP '^[0-9]+$'
    THEN SUBSTRING(input_str, LOCATE('ABC', input_str) + 3, LOCATE('_', input_str, LOCATE('ABC', input_str) + 3) - (LOCATE('ABC', input_str) + 3))
    ELSE NULL -- Return NULL if no valid match is found
  END AS extracted_digit
FROM (
  -- Test cases
  SELECT 'The real ABC4_ string is probably company secret.' AS input_str
  UNION ALL
  SELECT 'ABC123_ test ABC456_ another string' AS input_str -- Multiple ABCs, should pick first set of digits
  UNION ALL
  SELECT 'ABCxyz_ not a number here' AS input_str -- Non-numeric between ABC and _, returns NULL
  UNION ALL
  SELECT 'No ABC or underscore here' AS input_str -- No match, returns NULL
) AS test_data;

How It Works #

1. LOCATE('ABC', input_str): Finds the starting position of the first "ABC" in the string. If it returns 0, there's no match.
2. LOCATE('_', input_str, start_pos): Starts searching for "_" right after the end of the first "ABC" (position + 3, since "ABC" is 3 characters long). This ensures we only target the underscore that follows the first ABC.
3. SUBSTRING(...): Extracts the substring between the end of "ABC" and the next "_".
4. REGEXP '^[0-9]+$': Validates that the extracted substring is purely numeric—adjust this if you need to allow decimals or other formats, but your example uses integers.

Simplified Version (If You Don't Need Numeric Validation) #

If you're confident the value between ABC and _ will always be a digit, you can skip the regex check for brevity:

SELECT 
  IF(
    LOCATE('ABC', input_str) > 0 AND LOCATE('_', input_str, LOCATE('ABC', input_str) + 3) > 0,
    SUBSTRING(input_str, LOCATE('ABC', input_str) + 3, LOCATE('_', input_str, LOCATE('ABC', input_str) + 3) - (LOCATE('ABC', input_str) + 3)),
    NULL
  ) AS extracted_digit
FROM your_table;

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