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>

Just in case you don't already know about this, please read up on SQL injection: http://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. — Neville Kuyt, Aug 24 '11 at 14:50
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 — , Aug 24 '11 at 17:18

piotrp · Accepted Answer · 2011-08-24T14:48:51.207

32

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

edited Aug 24 '11 at 14:48

answered Aug 24 '11 at 14:39

piotrp

3,755
1
24
26

For the second question use mariadb instead of mysql and exercise REGEXP_REPLACE() – lrkwz Apr 19 '18 at 14:37
I have used it this way in the php trim(preg_replace('/\r\n/', ' ', $text)); – Vivek Jain Dec 13 '19 at 10:53

Jacob · Answer 2 · 2011-08-24T15:27:12.217

8

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

This works for your first question.

edited Aug 24 '11 at 15:27

answered Aug 24 '11 at 14:22

Jacob

41,721
6
79
81

That's not a valid SQL syntax – piotrp Aug 24 '11 at 14:52
@Crack, yeah sorry about that. Way too hot and humid in Germany right now. – Jacob Aug 24 '11 at 15:27
Heh, share your sun. 19°C and rainy in Poland ;) – piotrp Aug 24 '11 at 15:40

score 1 · Answer 3 · answered Aug 24 '11 at 16:55

1

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.

answered Aug 24 '11 at 16:55

Derek Hogue

4,589
1
15
27

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 – Aug 24 '11 at 17:17

score 0 · Answer 4 · answered Mar 18 '21 at 15:16

0

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

answered Mar 18 '21 at 15:16

András

135
1
11

sql query to convert new line character to a html

4 Answers4

Linked