Removing whitespace before a specified string MySQL

Question

I have a database table called phpbb_posts - the column giving me difficulty is called post_text and is of varchar(255) type.

In the column post text, all of the 1200+ entries have some whitespace before the </t> tag which indicates the end of the entry. I want to remove this whitespace. Here's what it looks like:

I want to delete the whitespace before the last </t> in every field in the column, so that afterwards it looks like this:

<t>Battle Dart</t>
<t>War Dart</t>
<t>Francisca</t>
<t>Hurlbat</t>
etc...

How can I achieve this? I've looked at RTRIM, REPLACE and a few other functions but nothing seems to be working.

Not even sure if it is whitespace or if it is a new line... any help appreciated :)

Dri372 · Accepted Answer · 2021-06-17 15:27:20Z

1

It's new line, and you have just to use replace

UPDATE phpbb_posts SET post_text=REPLACE(post_text,"\n","");

If you have also some blanck, just redo a 2nd UPDATE with this new replace

answered Jun 17, 2021 at 15:27

Dri372

1,3315 silver badges16 bronze badges

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

1 Comment

Teebling Over a year ago

Thanks for this - I added a 'WHERE' clause as well so that it didn't target posts before a certain time (because the new lines only appeared on posts after this time). UPDATE phpbb_posts SET post_text=REPLACE(post_text,"\n","") WHERE post_time>=1623887666

Collectives™ on Stack Overflow

Removing whitespace before a specified string MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related