SQL: Remove HTML with procidure

Clean dirty html with sqlI 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.

It's only fair to share...Buffer this pageShare on Facebook0Tweet about this on TwitterShare on Google+0Share on LinkedIn0Share on Reddit0Pin on Pinterest0Email this to someone
About

Just a guy with strong interest in PHP and Web technologies

Tagged with: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*