MySQL Query

[felix001]

Can anyone help ? Im looking to find a MySQL query using regular expressions to allow me to remove any paragraph html tags within block quote tags. An example is :

<blockquote>
<p>TEXT</p>
</blockquote>

Should be :

<blockquote>
TEXT
</blockquote>

Thanks,

[griswolf]

Tough to write an RE To deal with all legal blockquotes. For instance

<blockquote>
  this is badly placed but legal html (and probably rendered "correctly"<p>
  <blockquote>
     this is a bogus close tag, bad html, but ignored by most browsers</p>
     <blockquote>
     <p>This should be cleaned</p>
     <p>
so should this</p><p>and
this
</p></blockquote><p    >
This should probably be cleaned too, even though the tags are malformed: Firefox, Safari and Chrome render them as though they were correct.
</p     >
</blockquote>
<p>Should this one be cleaned, even though it is bad xml? It would still render with most browsers.
</blockquote>

You also fail to tell us why this is a problem for SQL. I presume you have complete documents stored as text in your table? I think If you don't have a huge amount of data, you are better served using some external language to parse each hit that results from approximately SELECT text_block from my_table where text_block LIKE '%<blockquote%<p%'; Maybe even if you do have a huge amount of data, since I doubt you can find a good enough RE (unless you already know that the blockquotes are very stylized, without exception).

[smantscheff]

Also keep in mind that MySQL doesn't have a regex replace function. You might find the pattern, but you cannot replace it. You would have to use a user defined function - which might be the easiest way to tackle it, anyway, if you really have to solve it within MySQL and not, as griswolf suggested, with an external regex program.