Unfortunately, there is no core function to strip HTML tags from a MySQL field. But, if you really need this to be done on MySQL side, you can use a stored function to remove all HTML tags from your text data.


To add this function to MySQL, first, you need to connect to MySQL either using SSH or phpMyAdmin. I used phpMyAdmin, which is rather more user-friendly and visual.

After you connect to phpMyAdmin, select your database and select the SQL menu item (or button) from the top actions menu. Paste the following code snippet to the text area and click the Go button underneath the field.

CREATE FUNCTION strip_tags(s TEXT) RETURNS TEXT 
NOT DETERMINISTIC NO SQL 
SQL SECURITY DEFINER 
BEGIN 
  DECLARE start,end INT DEFAULT 1; 
  LOOP 
    SET start = LOCATE("<", s, start);
    IF (!start) THEN RETURN s; END IF;
    SET end = LOCATE(">", s, start);
    IF (!end) THEN SET end = start; END IF;
    SET s = INSERT(s, start, end - start + 1, "");
  END LOOP;
END;

When you run this code on your database, it adds your function as a stored function to the database.


Strip Tags MySQL Function Usage

Stored functions are only run when you call them in a query. Of course, the field's data type you are using should match the function input data type. On this function, we used a TEXT data type.  So you can only use text data type fields while calling this function.

Here is a sample query for stripping all HTML tags from WordPress post_content field. I also used LENGTH function to display the content length of the posts:

SELECT post_content, strip_tags(post_content) as post_text, LENGTH(strip_tags(post_content)) FROM wp_posts WHERE wp_posts.ID = 1303

Please note that, on this sample code, I used only one post. If you need a full table of results, you need to remove all code starting with WHERE.


I hope you found this tip useful, Enjoy!

 

 

Join The Discussion (2 Responses)