How Can I Efficiently Search Database Records After Removing HTML Tags in MySQL?

Removing HTML Tags from Database Records

In MySQL, removing HTML tags from a specific column can be achieved using specific techniques. Consider the following data:

SELECT *
FROM table
WHERE colmn_name ` like '%mytext%'

This query returns all four rows even though only Row 3 contains "mytext" within the content. To obtain the desired result, Row 3 only, a more precise approach is required.

One solution involves using a user-defined function (UDF) to strip out the HTML tags. Here's an example:

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 ;

This function, fnStripTags(), iteratively locates and removes all HTML tags from the given input string. It can be used in a query as follows:

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

This revised query will return only Row 3, which contains "mytext" within its non-HTML content.

The above is the detailed content of How Can I Efficiently Search Database Records After Removing HTML Tags in MySQL?. For more information, please follow other related articles on the PHP Chinese website!