CODEWITHSUNDEEP
mysqlreplacelongtext
Richard Owens
Richard Owens

Reputation: 155

MySQL Replace Part of String (Longtext)

After a CONCAT from various other fields, I am trying to clean up the data - specifically removing multiple separators... but it affects 0 records (out of several thousand.)

UPDATE CONTACTS
SET NOTES = REPLACE(NOTES, "%- - - -%", "-")
WHERE NOTES = 53388 LIMIT 1

The 'where' is just for testing.

Example Before

ID | NOTES
1 | - - - - Hi there
2 | Sun is hot today - - - -
3 | Nice - - - - to be on stackoverflow
4 | This record is just - fine.

Required Result

ID | NOTES
1 |  Hi there
2 | Sun is hot today 
3 | Nice  to be on stackoverflow
4 | This record is just - fine.

I've checked and double checked the statement but can't figure out what I'm doing wrong.

It doesn't affect multiple rows or individual rows.

The field is longtext is that makes any difference?

Any ideas?

Upvotes: 0

Views: 830

Answers (2)

Jagrut Sharma
Jagrut Sharma

Reputation: 4754

You can try this:

UPDATE CONTACTS SET NOTES = REPLACE(TRIM(NOTES),'- - - -','');

Result:

Before:

SELECT * FROM CONTACTS;
+------+-------------------------------------+
| ID   | NOTES                               |
+------+-------------------------------------+
|    1 | - - - - Hi there                    |
|    2 | Sun is hot today - - - -            |
|    3 | Nice - - - - to be on stackoverflow |
|    4 | This record is just - fine.         |
+------+-------------------------------------+

After:

SELECT * FROM CONTACTS;
+------+------------------------------+
| ID   | NOTES                        |
+------+------------------------------+
|    1 |  Hi there                    |
|    2 | Sun is hot today             |
|    3 | Nice  to be on stackoverflow |
|    4 | This record is just - fine.  |
+------+------------------------------+

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

You're trying to replace this %- - - -% with -. In your sample data there are no % signs. You've probably mistaken it with % sign which matches any sequence of zero or more characters in LIKE expression.

Just use it without percent sign, and add additional spaces before/after if needed:

REPLACE(NOTES, "- - - -", "-")

If you need something more sophisticated logic and have MySQL version 8.0+ then you could use REGEXP_REPLACE function.

Upvotes: 0

Related Questions