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

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.