Skip to content

Instantly share code, notes, and snippets.

@QWp6t

QWp6t/gist:5168151

Created March 15, 2013 07:45
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save QWp6t/5168151 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save QWp6t/5168151 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. QWp6t created this gist Mar 15, 2013.
    49 changes: 49 additions & 0 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,49 @@
    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`);