How to Accurately Search MySQL Columns Containing HTML Tags?-Mysql Tutorial-php.cn

How to Accurately Search MySQL Columns Containing HTML Tags?

DDD

Release： 2024-12-14 10:05:10

Original

163 people have browsed it

How to Accurately Search MySQL Columns Containing HTML Tags?

Remove HTML Tags from a Record

You may have encountered a situation where you need to search for a specific keyword within a column that contains HTML tags. However, you might notice that your query returns incorrect results due to the presence of these tags. Let's explore how to modify your MySQL query to accurately filter records based on their content while excluding HTML tags.

In the provided example, the query SELECT * from table WHERE colmn_name LIKE '%mytext%' will retrieve all four rows, even though only row 3 contains the keyword "mytext" in its content. To overcome this challenge, consider the following solution proposed by a MySQL forum member:

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 ;
SELECT fnStripTags('this <html>is <b>a test</b>, nothing more</html>');

Copy after login

This solution involves creating a user-defined function (UDF) named fnStripTags that accepts a dirty string containing HTML tags. The function iterates through the string, locating opening (<) and closing (>) tags. It calculates the length of the tag and removes it from the string to produce a clean string without HTML elements.

To use this UDF, you can incorporate it into your original query as follows:

SELECT * FROM table WHERE fnStripTags(column_name) LIKE '%mytext%';

Copy after login

By utilizing this UDF, your query will exclude HTML tags from the search, ensuring that you retrieve only rows where the keyword "mytext" appears in the actual content. This approach allows you to perform more precise data filtering and avoid any interference from HTML markup.

The above is the detailed content of How to Accurately Search MySQL Columns Containing HTML Tags?. For more information, please follow other related articles on the PHP Chinese website!