Detect line breaks in MySQL?

Question

I've been searching for hours now but can't find anything that actually works.

I've got this in multiple records :

<p style="text-align: justify;"> SOME TEXT </p> <p style="text-align: justify;"> MORE TEXT </p>

I want to change it to this :

<p style="text-align: justify;"> SOME TEXT MORE TEXT </p>

I want to keep the line break but delete the first end tag and the second start tag.

I tried this :

UPDATE my_table SET my_collumn = REPLACE(my_collumn,'</p> <p style="text-align: justify;">','')

BUT it doesn't detect it because of the LINE BREAK between it.

How can I solve this?

Many thanks

The first snapshot where you show two lines, it that stored in a SINGLE MySql record ? — Darius X.
– Darius X., Commented Jul 23, 2013 at 15:49

Aaron Miller · Accepted Answer · 2013-07-23 15:38:20Z

2

If possible, you'd probably be better off doing this sort of replacement in your language of choice, which will offer stronger string handling capabilities than MySQL. That said, MySQL recognizes several C-style character escapes in strings, including both \r and \n; a CRLF in a MySQL string is therefore just '\r\n'.

answered Jul 23, 2013 at 15:38

Aaron Miller

3,8081 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

itachi42 Over a year ago

Thank you for your answser. When I search for '\r\n', it finds loads of occurrences. But if I add </p> or <p style="text-align: justify;"> It doesn't find anything anymore. Any idea why ? </p>\r\n<p style="text-align: justify;">

Aaron Miller Over a year ago

There may be spaces on either side of the newline, or something else non-obvious -- that's why I suggest, if possible, doing this kind of transform in code, instead of query, and preferably in a language offering strong regular expression support.

Aaron Miller Over a year ago

@itachi42 If you can, do it in code, as DanFromGermany has also suggested -- otherwise, I'm not really sure what to suggest, save 'try, try again'.

Daniel W. · Accepted Answer · 2013-07-23 15:41:25Z

I suggest you not to use MySQL for the string operations. This is not what a Database is made for. Use PHP, Perl, ASP, whatever you are coding with.

Problems you might run into:

Instead of common line break \r\n between the tags, you might have to parse different cases:

<blankspace>\r\n \n\n \n \r\n<blankspace>\r\n ...

someday, you also might want to change

<p style="text-align: justify;">

to

<p class="textClass">

Then you'd need to change the SQL again.

If you really wonna do it, have a look at UDFs like

Regex Replace in MySQL

I'm storing all the content into databases so I can change the whole HTML layout ;) so yeah, I'm planning to use CSS

Andomar · Accepted Answer · 2013-07-23 15:33:01Z

0

In SQL Server, you can use char(13) + char(10) to include a newline:

'</p>' + char(13) + char(10) + '<p ...>'

The meaning of the numbers can be found in the ASCII chart:

13 = carriage return 10 = newline

answered Jul 23, 2013 at 15:33

Andomar

239k55 gold badges387 silver badges412 bronze badges

4 Comments

itachi42 Over a year ago

I'm looking for the same in MySQL... :/

Barmar Over a year ago

Have you looked at the MySQL documentation of string functions? There are analogs to everything in his answer.

Barmar Over a year ago

In fact, it has CHAR(), so the only difference is that you use CONCAT() to concatenate strings instead of the + operator.

Andomar Over a year ago

For MySQL, check out Aaron Miller's answer.

Collectives™ on Stack Overflow

Detect line breaks in MySQL?

3 Answers 3

3 Comments

1 Comment

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

4 Comments

Related