CODEWITHSUNDEEP
htmlmysqlsqlreplaceline-breaks
itachi42
itachi42

Reputation: 303

Detect line breaks in MySQL?

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

Upvotes: 3

Views: 2475

Answers (3)

Daniel W.
Daniel W.

Reputation: 32290

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

Upvotes: 1

Aaron Miller
Aaron Miller

Reputation: 3780

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'.

Upvotes: 2

Andomar
Andomar

Reputation: 238086

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

Upvotes: 0

Related Questions