Strip HTML in MySQL

: Joel Lipman; MySQL; 06 December 2011; Hits: 11794

Just putting a note as I have spent ages looking for a solution and getting it to work in my environment.

What?
Need to be able to omit HTML tags in certain fields of a mySQL database.

Why?
We are preparing to migrate old content to a new system. From a MediaWiki CMS to a SaaS called Service-Now. The previous interlinking between images could no longer be used.

How?
There were a few functions out there. One called "strip_tags" crashed in the running of the query. The function that did work errored persistently complaining about the delimiter, create the function by running the following:

copy raw

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 ;

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 ;

On my system, this reported an error but still created the function. This should create a function called "fnStripTags", which if the above is causing more problems than solving, can be overwritten using a GUI with the following:

copy raw

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

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

To use this

copy raw

SELECT
	p.pretendColumn1 AS Column1,
	CONCAT('Textual Contents: <br />', fnStripTags(p.pretendColumn2)) AS Column2
FROM
	pretendTable p

SELECT
p.pretendColumn1 AS Column1,
CONCAT('Textual Contents: <br />', fnStripTags(p.pretendColumn2)) AS Column2
FROM
pretendTable p

Category: MySQL :: Article: 405

Strip HTML in MySQL

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support