Sql query to convert new line character to html

Question

I have a mysql database of articles that were entered into the text field without formatting, they only use the newline character for line breaks

\n 

I need to convert all this to html br tags

 <br /> 

Can you help me write a request to do this, what can I run in phpmyadmin to do this?

table name

 exp_channel_data 

---

as a bonus question ...

Is there a request that I can run that will cut everything from 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 finish with this

 <p> <span> 

Answer 1

First question:

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

If it does not replace anything, use '\n' instead of '\r\n' .

Second question:

You cannot do this with a SQL query, you need to extract this data in a PHP script or anything else that you like and perform a regular expression replacement (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.

Answer 3

Since you are using ExpressionEngine, you can simply change the format of each field to XHTML. It will add markup <br /> if it is displayed on the interface. place a bet to keep your data clean and to leave formatting for the analyzer displaying it.