variable lengh string replacements in MySQL without PCRE UDF extension

sql-replacements.sql

-- variable lengh replacements without PCRE UDF extension
-- replaces only one different matched string in each pass!


-- replace string of variable length with known beginning and end
SET @start = 'xxx';
SET @finish = 'yyy';
SET @replacement = '';
UPDATE `table` SET
  `text` = REPLACE(
    @text := `text`,
    SUBSTR(@text, LOCATE(@start, @text), LOCATE(@finish, @text, LOCATE(@start, @text) + LENGTH(@start)) - LOCATE(@start, @text) + LENGTH(@finish)),
    @replacement
  )
WHERE `text` LIKE CONCAT('%', @start, '%', @finish, '%');


-- replace link tag with its address

UPDATE `table` SET
`text` = REPLACE(
    @text := `text`, 
    SUBSTR(@text, LOCATE('<a href="', @text), LOCATE('</a>', @text, LOCATE('<a href="', @text) + 10) - LOCATE('<a href="', @text) + 4),
    SUBSTR(@text, LOCATE('<a href="', @text) + 9, LOCATE('"', @text, LOCATE('<a href="', @text) + 10) - LOCATE('<a href="', @text) - 9)
  )
WHERE `text` LIKE '%<a href%';