MySQL: UDF to remove HTML Tag from the String Data

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a user defined function to remove all HTML tags from the string data of MySQL Server.

There are certain requirements like, store HTML document into the text column, but unfortunately it also contains HTML tags which required to remove.

I found a user defined function for removing all HTML Tags from the given string.

DELIMITER $$

CREATE FUNCTION fn_RemoveHTMLTag (HtmlString text) RETURNS text

BEGIN

DECLARE StartTag, EndTag INT DEFAULT 1;

LOOP

SET StartTag = LOCATE("<", HtmlString, StartTag);

IF (!StartTag) THEN

RETURN HtmlString;

END IF;

SET EndTag = LOCATE(">", HtmlString, StartTag);

IF (!EndTag) THEN

SET EndTag = StartTag;

END IF;

SET HtmlString = INSERT(HtmlString, StartTag, EndTag - StartTag + 1, "");

END LOOP;

END;

Sample Execution:

1	SELECT fn_RemoveHTMLTag(' This is a Database Research and Development ');

Mar 24, 2016Anvesh Patel

Comments: 5

kamal

April 14, 2016 at 1:14 pm

Good write-up, I’m normal visitor of one’s website, maintain up the excellent operate, and It is going to be a regular visitor for a long time.

Jim

March 15, 2017 at 5:59 am

Not worked on my mysql. Doesn’t remove any html tags.

Anvesh Patel
March 16, 2017 at 6:31 pm
Hello jim, this demo is working for me and I am using MySQL 5.7. Would you please share your error details.
preeti
January 3, 2020 at 8:29 am
On running this function I get error .LOCATE does not exist

preeti

January 3, 2020 at 8:25 am

I created this function using mysql query browser. It is created but when it is executed the error comes that: FUCNTION .LOCATE does not exist