MySQL 截取兩個字符串之間內容：LOCATE + SUBSTRING 實現字符串區間提取

MySQL 字符串區間提取：基於 LOCATE 與 SUBSTRING 的通用方案

需求：從字符串 "[]abcd[12345]ddx" 中提取 "abcd[" 與其後第一個 "]" 之間的內容 "12345"。MySQL 無 indexOf，但提供 LOCATE、SUBSTRING 組合實現。

一、存儲過程通用實現

DELIMITER $$

CREATE PROCEDURE `sp_str_between`(
    IN p_str VARCHAR(500),        -- 原始字符串
    IN p_begin_str VARCHAR(100),  -- 起始標記
    IN p_end_str VARCHAR(100),    -- 結束標記
    OUT p_result VARCHAR(500)     -- 提取結果
)
BEGIN
    DECLARE v_start_pos INT DEFAULT 0;
    DECLARE v_end_pos INT DEFAULT 0;

    -- 1. 找到起始標記結束位置
    SELECT LOCATE(p_begin_str, p_str) + CHAR_LENGTH(p_begin_str) INTO v_start_pos;

    -- 2. 從起始位置向後找結束標記
    SELECT LOCATE(p_end_str, p_str, v_start_pos) INTO v_end_pos;

    -- 3. 截取中間內容
    IF v_start_pos > 0 AND v_end_pos > 0 THEN
        SELECT SUBSTRING(p_str, v_start_pos, v_end_pos - v_start_pos) INTO p_result;
    ELSE
        SET p_result = NULL;
    END IF;
END $$

DELIMITER ;

二、調用測試

CALL sp_str_between('[]abcd[12345]aa[]ss', 'abcd[', ']', @result);
SELECT @result;  -- 12345

CALL sp_str_between('[]abcd[sdww]aa[]ss', 'abcd[', ']', @result);
SELECT @result;  -- sdww

三、單條 SQL 直接實現 (無需存儲過程)

SELECT SUBSTRING(
    ']abcd[12345]111[]',
    LOCATE('abcd[', ']abcd[12345]111[]') + CHAR_LENGTH('abcd['),
    LOCATE(']', ']abcd[12345]111[]', CHAR_LENGTH('abcd[')) - 
    (LOCATE('abcd[', ']abcd[12345]111[]') + CHAR_LENGTH('abcd['))
) AS extracted;
-- 結果：12345

四、核心函數解析

五、邊界情況處理

• 起始/結束標記不存在 → 返回 NULL
• 結束標記在起始標記前 → 返回 NULL
• 多組匹配 → 僅提取第一組 (LOCATE 返回首次出現)
• 需提取最後一組 → 反轉字符串或用 SUBSTRING_INDEX 配合

六、進階：正則表達式提取 (MySQL 8.0+)

SELECT REGEXP_SUBSTR('[]abcd[12345]ddx', '(?<=abcd\\[)[^\\]]+');
-- 12345
-- 正則解析：(?<=abcd\\[) 正向後顧斷言，匹配 abcd[ 後的位置；[^\\]]+ 匹配非 ] 字符一個或多個