Print

Strip HTML in MySQL

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:
copyraw 
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 ;
  1.  SET GLOBAL log_bin_trust_function_creators=1; 
  2.  DROP FUNCTION IF EXISTS fnStripTags; 
  3.  DELIMITER | 
  4.  CREATE FUNCTION fnStripTags( Dirty varchar(4000) ) 
  5.  RETURNS varchar(4000) 
  6.  DETERMINISTIC 
  7.  BEGIN 
  8.    DECLARE iStart, iEnd, iLength int; 
  9.    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO 
  10.      BEGIN 
  11.        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty )); 
  12.        SET iLength = ( iEnd - iStart) + 1; 
  13.        IF iLength > 0 THEN 
  14.          BEGIN 
  15.            SET Dirty = Insert( Dirty, iStart, iLength, ''); 
  16.          END; 
  17.        END IF; 
  18.      END; 
  19.    END WHILE; 
  20.    RETURN Dirty; 
  21.  END; 
  22.  | 
  23.  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:
copyraw 
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
  1.  BEGIN 
  2.    DECLARE iStart, iEnd, iLength int; 
  3.    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO 
  4.      BEGIN 
  5.        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty )); 
  6.        SET iLength = ( iEnd - iStart) + 1; 
  7.        IF iLength > 0 THEN 
  8.          BEGIN 
  9.            SET Dirty = Insert( Dirty, iStart, iLength, ''); 
  10.          END; 
  11.        END IF; 
  12.      END; 
  13.    END WHILE; 
  14.    RETURN Dirty; 
  15.  END 

To use this
copyraw 
SELECT
	p.pretendColumn1 AS Column1,
	CONCAT('Textual Contents: <br />', fnStripTags(p.pretendColumn2)) AS Column2
FROM
	pretendTable p
  1.  SELECT 
  2.      p.pretendColumn1 AS Column1, 
  3.      CONCAT('Textual Contents: <br />', fnStripTags(p.pretendColumn2)) AS Column2 
  4.  FROM 
  5.      pretendTable p 
Category: MySQL :: Article: 405