0

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?

Richard Owens
  • 155
  • 16

2 Answers2

1

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.  |
+------+------------------------------+
Jagrut Sharma
  • 4,574
  • 3
  • 14
  • 19
  • OK, there's some very odd behavior here. This suggestion in itself doesn't work but does if I just look for a single '-', where it replaces all instances!! I've gone over all the reconds and settled for a replacement of ' -' for '' which eliminates most of the duplicates. – Richard Owens Jun 29 '18 at 08:57
  • You can try and run this query to see how many records are candidates for update. `SELECT * FROM CONTACTS WHERE TRIM(NOTES) LIKE '%- - - -%';` One possibility is that the space may actually be some non-printing character. – Jagrut Sharma Jun 29 '18 at 09:06
  • You can take a look at this thread, and try the queries and see if you can find something odd (like non-printing characters) in `NOTES` column. https://stackoverflow.com/questions/401771/how-can-i-find-non-ascii-characters-in-mysql – Jagrut Sharma Jun 29 '18 at 09:31
0

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.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72