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:

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:

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

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

< Prev		Next >