Using MySQL 8.0 REGEXP_REPLACE and REGEXP_INSTR for Targeted Substring Replacement

MySQL has always supported regular‑expression matching, but the ability to perform regular‑expression replacement (REGEXP_REPLACE) was only added in MySQL 8.0. Before that, developers either wrote custom stored functions or performed the replacement outside the database.

1. Custom stored function – the following function demonstrates a hard‑coded three‑time replacement (intended only as a demonstration; such functions are discouraged in production):

DELIMITER $$
USE `ytt`$$
DROP FUNCTION IF EXISTS `func_instr_simple_ytt`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_instr_simple_ytt`(
    f_str VARCHAR(1000),
    f_substr VARCHAR(100),
    f_replace_str VARCHAR(100),
    f_times INT -- only supports 3
) RETURNS VARCHAR(1000)
BEGIN
    DECLARE v_result VARCHAR(1000) DEFAULT 'ytt';
    DECLARE v_substr_len INT DEFAULT 0;
    SET f_times = 3;
    SET v_substr_len = LENGTH(f_substr);
    SELECT INSTR(f_str, f_substr) INTO @p1;
    SELECT INSTR(SUBSTR(f_str, @p1 + v_substr_len), f_substr) INTO @p2;
    SELECT INSTR(SUBSTR(f_str, @p2 + @p1 + 2 * v_substr_len - 1), f_substr) INTO @p3;
    IF @p1 > 0 AND @p2 > 0 AND @p3 > 0 THEN
        SELECT CONCAT(
            SUBSTR(f_str, 1, @p1 + @p2 + @p3 + (f_times - 1) * v_substr_len - f_times),
            f_replace_str,
            SUBSTR(f_str, @p1 + @p2 + @p3 + f_times * v_substr_len - 2)
        ) INTO v_result;
    ELSE
        SET v_result = f_str;
    END IF;
    SET @p1 = NULL; SET @p2 = NULL; SET @p3 = NULL;
    RETURN v_result;
END$$
DELIMITER ;

Calling the function:

UPDATE y1 SET str1 = func_instr_simple_ytt(str1, 'action', 'dble', 3);

2. Export‑sed‑import workflow (recommended for large data sets)

Export the table to a CSV file: SELECT * FROM y1 INTO OUTFILE '/var/lib/mysql-files/y1.csv'; Replace the third occurrence of "action" with "dble" using sed: sed -i 's/action/dble/3' y1.csv Truncate the original table and reload the modified file:

TRUNCATE y1;
LOAD DATA INFILE '/var/lib/mysql-files/y1.csv' INTO TABLE y1;

3. Direct replacement with MySQL 8.0

Using the built‑in REGEXP_REPLACE function you can replace the third occurrence in a single statement:

UPDATE y1 SET str1 = REGEXP_REPLACE(str1, 'action', 'dble', 1, 3);

4. Finding the position of the nth occurrence

The REGEXP_INSTR function returns the start position of a pattern; specifying the occurrence argument lets you locate the second, third, etc. Example with a numeric pattern:

SET @a = 'aa bb cc ee fi lucy 1 1 1 b s 2 3 4 5 2 3 5 561 19 10 10 20 30 10 40';
SELECT REGEXP_INSTR(@a, '[:digit:]{2,}', 1, 2);

Example with multibyte Chinese characters:

SET @a = '中国 美国 俄罗斯 日本 中国 北京 上海 深圳 广州 北京 上海 武汉 东莞 北京 青岛 北京';
SELECT REGEXP_INSTR(@a, '北京', 1, 1);  -- returns 17
SELECT REGEXP_INSTR(@a, '北京', 1, 2);  -- returns 29
SELECT REGEXP_INSTR(@a, '北京', 1, 3);  -- returns 41

Conclusion

MySQL 8.0 provides two powerful regular‑expression functions—REGEXP_REPLACE and REGEXP_INSTR—that together offer a clean, efficient solution for replacing the nth occurrence of a substring and for locating that occurrence, eliminating the need for custom functions or external processing.