sql query to convert new line character to a html <br>

Question

I have a mysql database of articles that were entered into a textarea with no formatting, they use only a newline character for line breaks

\n

I need to convert all of these into html br tags

<br />

can you help me write a query to do this that I can run in phpmyadmin that will do this?

the name of the table is

exp_channel_data

---

as a bonus question...

Is there a query I can run that will strip everything out of the middle of p and span tags

I want to get rid of this stuff

<p class="MsoNormal" style="MARGIN: 0in 0in 0pt">
<span face="Times New Roman">

and end up with just this

<p>
<span>

Answer 1

First question:

UPDATE exp_channel_data SET text_column = REPLACE(text_column, '\r\n', '<br />')

If it doesn't replace anything, use '\n' instead of '\r\n'.

Second question:

You can't do it with a SQL query, you have to fetch this data into a PHP script, or anything else you like, and perform a regular expression replace (example for PHP):

$new_str = preg_replace('#<(p|span)[^>]+>#', '<$1>', $old_string);

Answer 2

UPDATE yourTable SET text=REPLACE(text,"\n","<br />")

This works for your first question.