Good day.

A problem of this nature is stored in the database content dynamically generated on the site pages. Stored along with HTML tags. When a query is made to the database to search for records like the specified string, mysql finds entries in both text and HTML tags. Is it possible to make Mysql ignore HTML?

  • 2
    A living example of why it is bad to store html in the database, instead of data - Mr. Black
  • one
    stackoverflow.com/a/13409726/5006740 - strangeqargo
  • no .. it is necessary to process the received text later on php / js, etc. - Volodymyr
  • “Only the text is stored in the database” and “stored with the HTML tags” - the phrases are mutually exclusive, please decide - andreymal
  • I need that during the execution of the query in the database, the server ignored entries that match the search string in the HTML tag. I need to avoid re-iterating records in PHP - Vladimir Prokopchuk

1 answer 1

In principle, there is such an opportunity. You need to create your own function that will remove tags from the text:

DELIMITER // CREATE FUNCTION strip_tags(str text) RETURNS text DETERMINISTIC BEGIN DECLARE start, end INT DEFAULT 1; IF str IS NULL THEN RETURN NULL; END IF; LOOP SET start = LOCATE("<", str, start); IF (!start) THEN RETURN str; END IF; SET end = LOCATE(">", str, start); IF (!end) THEN SET end = start; END IF; SET str = INSERT(str, start, end - start + 1, ""); END LOOP; RETURN str; END; //

After its creation, we can perform like-by-text without tags:

 select * from table where strip_tags(html_field) like '%text%'

But, we must understand that the execution of the function itself over each value from the column takes time. Plus, no indexes for such a query can be used in principle, which will lead to a full scan of the table with each query. Therefore, if the table is large, it is strongly recommended to store the finished text without tags in a separate field and it is possible to use full-text search.