SQL function(s) to convert phpBB3 HTML URLs to BBCODE. In particular, I used this when migrating from phpBB3 to MyBB. These two functions could easily be refactored for doing many other HTML->BBCODE conversions. Just follow the patterns.

gistfile1.sql

DELIMITER ||
DROP FUNCTION IF EXISTS GET_DATA||
CREATE FUNCTION GET_DATA( _data LONGTEXT, _begin LONGTEXT, _end LONGTEXT) RETURNS LONGTEXT
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE _startPos INT UNSIGNED;
    DECLARE _endPos INT UNSIGNED;
    SET _startPos = LOCATE(_begin, _data, 1);
    IF _startPos < 1 THEN RETURN NULL; END IF;
    SET _startPos = _startPos + LENGTH(_begin);
    SET _endPos = LOCATE(_end, _data, _startPos);
    RETURN SUBSTRING(_data,_startPos,_endPos - _startPos);
END;
||
DELIMITER ;

# SELECT GET_DATA( `message` , '<!-- m --><a class="postlink" href="', '">' )
# FROM `mybb_posts`
# WHERE GET_DATA( `message` , '<!-- m --><a class="postlink" href="', '">' ) IS NOT NULL
# LIMIT 0,5077;

DELIMITER ||
DROP FUNCTION IF EXISTS PHPBB3_TO_MYBB_URLS||
CREATE FUNCTION PHPBB3_TO_MYBB_URLS( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE _linkURL VARCHAR(500);
    DECLARE _linkText VARCHAR(500);
    DECLARE _begin VARCHAR(500);
    DECLARE _middle VARCHAR(500);
    DECLARE _end VARCHAR(500);
    DECLARE _startPos INT UNSIGNED;
    DECLARE _endPos INT UNSIGNED;
    SET _begin = '<!-- m --><a class="postlink" href="';
    SET _middle = '">';
    SET _end = '</a><!-- m -->';
    LOOP
        SET _linkURL = GET_DATA(x,_begin,_middle);
        IF (_linkURL IS NULL) THEN RETURN x; END IF;
        SET _linkText = GET_DATA(x,CONCAT(_linkURL,_middle),_end);
        SET x = REPLACE(x,CONCAT(_begin,_linkURL,_middle,_linkText,_end),CONCAT('[url=',_linkURL,']',_linkText,'[/url]'));
        SET _linkURL = GET_DATA(x,_begin,_middle);
    END LOOP;
    RETURN x;
END;
||
DELIMITER ;

# UPDATE `mybb_posts` SET `message` = PHPBB3_TO_MYBB_URLS(`message`);