Remove parts of cell data

[djp1988]

I have a forum, and I'm looking to add a search feature. My problem is my "content" cell where the message body is saved, I have line break tags and image tags.When someone searches, I want to ignore the content within these tags.I thought that I could create a view with the content cell treated and the image tag removed, how would I do this in SQL?

[justsomeguy]

It depends which database you're using:http://www.google.com/search?client=opera&...-8&oe=utf-8

[djp1988]

Sure I'm just using MySQL, I've created views before, the thing I guess I am after is any useful mysql functions that I could use on the cell to remove parts of its content

[justsomeguy]

Here are the string functions:http://dev.mysql.com/doc/refman/5.0/en/string-functions.htmlYou may need to use a combination of locate, substr, and replace to find "<" and ">" characters and remove everything between them.

[djp1988]

Yes unfortunatly MySQL's regex function isn't smart enough to perform replace, only tells you if there's a match or not.I've made a function I'm using to remove the unwanted info, if anyone's interested in improving it or using it themselves, feel free

delimiter //create function removeTags(txt longtext) returns longtextbegin declare start_tag char(5);declare end_tag char(2);declare end_pos int;declare new_txt longtext;set start_tag = '<img';set end_tag = '/>';set new_txt = REPLACE(txt,'<br />',' ');set new_txt = REPLACE(new_txt,'<p>',' ');set new_txt = REPLACE(new_txt,'</p>',' ');while locate(start_tag,new_txt) != 0 do	set end_pos = LOCATE(end_tag,new_txt)+2;	set new_txt = CONCAT(SUBSTRING_INDEX(new_txt,start_tag,1),SUBSTRING(new_txt,end_pos));end while;return new_txt;end//

In use:

select text_cellfrom table1 where removeTags(text_cell) like '%what ever search%'