PHP's strip_tags() equivalent MYSQL function

PHP
1

Hello forums!
I was just wondering to know how this can be accomplished:
Case:

SELECT * FROM cms_pages WHERE body LIKE '%keywords%';

Problem: Suppose my keyword=font & body field contains:

<div style="font-size:12px">This is a body area</div>

then above query fetches this row.

I would like to know,
is there any function to strip the html tags while executing query?
something like:

SELECT * FROM cms_pages WHERE strip_tags(body) LIKE '%keywords%';

or by any other means?

Thanks

2

Find here an interesting discussion and a solution by writing a mysql function
http://forums.mysql.com/read.php?52,177343,177985#msg-177985

3

Custom function strip_tags():

delimiter ||
DROP FUNCTION IF EXISTS strip_tags||
CREATE FUNCTION strip_tags( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE sstart INT UNSIGNED;
DECLARE ends INT UNSIGNED;
SET sstart = LOCATE('<', x, 1);
REPEAT
SET ends = LOCATE('>', x, sstart);
SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ;
SET sstart = LOCATE('<', x, 1);
UNTIL sstart < 1 END REPEAT;
return x;
END;
||
delimiter ;

Usage:

SELECT strip_tags('<div style="font-size:12px">This is a body area</div>') as stripped_text;

Reference: dev.mysql.com

Any alternatives?

4

Isn’t this good enough for your purpose? The function at my above link works perfectly fine I guess. Did you find any pitfalls with the function?

5

your link also just work fine.
I just added something new.

I found an alternative way which doesn’t use mysql’s custom function.
ie. store the body data in two fields: body_plain for storing html stripped data & body_html for storing html data.
search will be done against body_plain & data will be shown from body_html and i think this is perfect in the case like mine.

6

Yes indeed. But is that what you were searching as a solution? Adding an extra field is not just a solution that is the extra burden to the database which needs to store the almost same bulk data in two fields (twice same data) but still it is a reliable one. But it is obvious that can be a solution if you cannot trust on that mysql function. Since you were searching some solutions in mysql, both solutions are within the mysql i guess. Go for one then!