sql query to convert new line character to a html

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

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

This works for your first question.

Answer 2

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 3

Since you're using ExpressionEngine, you can just change the format of each field to XHTML. It will add the <br /> markup when displayed on the front-end. bet to keep your data clean and leave the formatting to the parser displaying it.