How Can I Search a Database Column for Text While Ignoring HTML Tags?

Removing HTML Tags from Database Records

You're attempting to extract rows from a database table where a specific column contains the keyword "mytext" but are encountering incorrect results. The table contains rows with varying degrees of HTML tags enclosing the string. To accurately identify the rows containing the desired text, it's crucial to exclude text wrapped in HTML tags.

One possible solution involves utilizing a custom MySQL function known as fnStripTags. This function takes a string as input and removes all HTML tags, providing a purely textual output.

Code:

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 ;

Usage:

Once the function is defined, you can use it in your query to exclude HTML tags from the search:

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

This modified query will return only the rows where the stripped text contains the string "mytext," providing you with the correct result:

Row 3: <p>This is the Third row  my mytext is there  </p>

The above is the detailed content of How Can I Search a Database Column for Text While Ignoring HTML Tags?. For more information, please follow other related articles on the PHP Chinese website!