CODEWITHSUNDEEP
mysql
Bruno9779
Bruno9779

Reputation: 1669

Mysql replace matching and replacing invalid characters

I have a weird hyphen in one of the mysql tables created by Racktables. I am trying to replace it with a normal one, but I seem to be missing something:

mysql> update Port set reservation_comment = replace(reservation_comment,'–','-');
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2358  Changed: 0  Warnings: 0

As you can see the "bad" hyphen gets matched but not replaced. I have tried changing single quotes to double quotes and escaping the hyphens, but there is no real change.

Here goes some sample data:

| 2690 |       767 | R131226-005-23Ha |      1 |   24 | NULL         | C130527-059                                  |         |
| 2691 |       768 | R131226-005-24Ha |      1 |   24 | NULL         | C130527-036                                  |         |
| 2692 |       770 | R131226-006-01Ha |      1 |   24 | NULL         | C140305�001                                  |         |
| 2693 |       773 | R131226-006-04Ha |      1 |   24 | NULL         | C140305�004                                  |         |
| 2694 |       784 | R131226-006-15Ha |      1 |   24 | NULL         | C140305�015                                  |         |
| 2695 |       785 | R131226-006-16Ha |      1 |   24 | NULL         | C140305�016                                  |         |
| 2696 |       793 | R131226-006-24Ha |      1 |   24 | NULL         | C140305�024                                  |         |
| 2697 |       771 | R131226-006-02Ha |      1 |   24 | NULL         | C140305�002                                  |         |
| 2698 |       772 | R131226-006-03Ha |      1 |   24 | NULL         | C140305-003

The hyphen I am trying to replace is encoded differently it seems

EDIT:

SO, all syntax below is good. The problem is that I cannot match the bad hyphen. If I try to replace the "good" one all is fine:

mysql> UPDATE Port     SET reservation_comment=REPLACE(reservation_comment,'-','[[good_hyphen]]');
Query OK, 367 rows affected (0.34 sec)
Rows matched: 2358  Changed: 367  Warnings: 0

but if I try to replace the bad one:

mysql> UPDATE Port     SET reservation_comment=REPLACE(reservation_comment,'–','[[bad_hyphen]]');
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2358  Changed: 0  Warnings: 0

I will look into the encoding of that character and into different ways to match it.

Upvotes: 0

Views: 564

Answers (2)

Bruno9779
Bruno9779

Reputation: 1669

Ok, I solved it by doing something similar to this post once I identified the character:

mysql> select hex("–");
+------------+
| hex("–") |
+------------+
| E28093     |
+------------+
1 row in set (0.00 sec)

Then I verified that matched:

mysql> select x'E28093';
+-----------+
| x'E28093' |
+-----------+
| –       |
+-----------+
1 row in set (0.00 sec)

And finally the substitution: 

mysql> UPDATE Port     SET reservation_comment=REPLACE(reservation_comment,x'E28093','-');
Query OK, 33 rows affected (1.55 sec)
Rows matched: 2358  Changed: 33  Warnings: 0

Upvotes: 1

Arth
Arth

Reputation: 13110

That is matching all rows, regardless of the hyphen as there is no WHERE clause. It is just not changing rows where there is no bad hyphen.

It may be that the bad hyphen is getting stored as an unknown character, so will not be targeted by your REPLACE. You may have to change the charset/collation on your column to get it to import correctly, before changing.. or adjust the way that Racktables outputs the data.

Upvotes: 1

Related Questions