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?
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. |
+------+------------------------------+