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.

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 *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.