[plug] Stripping out Markups in SQL

[Jason Bainbridge]

On Tue, 17 Sep 2002 11:08, Trevor Phillips wrote:

> I know how to do it in any number of languages, but I really need to do it
> in the SQL query itself, since I both sort and filter using a Markup-less
> version of a text field.
>
> Sure, I could just get every match, and filter in code, but that would be
> very inefficient.

Been thinking about this one, as you probably already know stripping out all 
html tags with a replace statement or two is a no brainer, but if you take a 
few extra steps  with some nested replace statements you could achieve the 
effect of stripping out all tags except those you care about.

eg. column name is body_text and the replace statement for the particular DBMS 
is replace(column_name,'text to replace','new text') with only the IMG tag to 
remain after the stripping:

Prepare yourself, this one is messy and probably a real beotch performance 
wise but if you're stuck with pure SQL I don't know if there is much else you 
can do:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(body_text,'<IMG>','|IMG|'),'</IMG>','|/IMG|'),'<','<'),'>','>'),'|IMG|','<IMG>'),'|/IMG|','</IMG>')

Told ya it was messy. :) I hope I got the syntax right but I'm sure you get 
the picture...

-- 
Jason Bainbridge
KDE - Conquer Your Desktop
The KDE Usability Project - http://usability.kde.org
http://kde.org - webmaster at kde.org