SQL: Remove HTML with procidure

Posted on November 24, 2012 by

I had a problem with client’s data that was saved as HTML in the database and I needed it in plain format. Many would say this is very inefficient way of doing it but sometimes desperate time call for desperate measures. And here is my solution to the problem:

[cc lang=”sql” escaped=”true”]
SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC
BEGIN
DECLARE iStart, iEnd, iLength int;
    WHILE Locate( ‘<‘, Dirty ) > 0 And Locate( ‘>’, Dirty, Locate( ‘<‘, Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( ‘<‘, Dirty ), iEnd = Locate( ‘>’, Dirty, Locate(‘<‘, Dirty ));
        SET iLength = ( iEnd – iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, ”);
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;
|
DELIMITER ;
SELECT fnStripTags(‘<p>Here goes your HTML</p>’);
[/cc]
With some string manipulations we can remove all of the tags and output the clean text.