Skip to content

Instantly share code, notes, and snippets.

@paranoiq
Last active August 29, 2015 14:08
Show Gist options
  • Save paranoiq/b4f8bb87d7ae0d47e6a0 to your computer and use it in GitHub Desktop.
Save paranoiq/b4f8bb87d7ae0d47e6a0 to your computer and use it in GitHub Desktop.
variable lengh string replacements in MySQL without PCRE UDF extension
-- 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%';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment