Skip to content

Instantly share code, notes, and snippets.

@QWp6t QWp6t/gist:5168151
Created Mar 15, 2013

Embed
What would you like to do?
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.
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`);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.