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>

Faustinafaustine

Just in case you don't already know about this, please read up on SQL injection: en.wikipedia.org/wiki/SQL_injection. You really, really, don't want to have SQL in your database. Also, in general, I'd strongly recommend doing this kind of formatting work outside the database. – Pedraza

ya I'm actually not including any SQL in the database (I think), this is just a one time conversion I need to run, appreciate the tip – Faustinafaustine

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);

Caslon

For the second question use mariadb instead of mysql and exercise REGEXP_REPLACE() – Balboa

I have used it this way in the php trim(preg_replace('/\r\n/', '
', $text)); – Extracellular

Answer 2

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

This works for your first question.

Tremolant

That's not a valid SQL syntax – Caslon

@Crack, yeah sorry about that. Way too hot and humid in Germany right now. – Tremolant

Heh, share your sun. 19°C and rainy in Poland ;) – Caslon

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.

Alesandrini

I'm actually switching from a textarea fieldtype with auto br formatting to NSM TinyMCE and want to preserve the breaks, if I don't do this the text all runs together, if I wasn't switching fieldtypes your solution would be a good one – Faustinafaustine

Answer 4

IF someone happens to look for a PHP-side solution, use the nl2br function... it worked for me. It converts those nasty line breaks to HTML <br> tags during runtime.

https://www.w3schools.com/PHP/func_string_nl2br.asp

Deeply