I did the following test...
1) Create a table with some data:
create table test(col varchar(10));
insert into test values ('abc'), ('dbe');
2) Select number of rows using your same filter (but different character):
select count(*)
from test
where col like '%B%' -- note the uppercase
;
Got the following result:
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set
3) Tried your update:
update test
set col = replace(col, 'B', '') -- note the uppercase
where col like '%B%' -- note the uppercase
;
And got this result:
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2 Changed: 0 Warnings: 0
In my case, a default character set and collation where used on table creation. The default character set was 'latin1' and collation 'latin1_swedish_ci'. Note the ci at the end of the collation.... it means case insensitive. So, the LIKE filter did a case insensitive search, found 2 rows, but the REPLACE function, as can be seen on documentation, is case sensitive. Probably, as in my case, the update found the same number of rows as in the select, but updated less data because of the case restriction on REPLACE.
If this is your problem, can't you just run two updates, one for the uppercase case and one for the lowercase? I'll try to develop a solution on one update...
The docs about the REPLACE(str, from_str, to_str) function:
Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.
The docs about the LIKE operator:
The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a case sensitive (uses a case-sensitive collation or is a binary string):
The first example:
mysql> SELECT 'abc' LIKE 'ABC';
-> 1
The second example:
mysql> SELECT 'abc' LIKE _latin1 'ABC' COLLATE latin1_general_cs;
-> 0
Note the cs at the end of the collation. It means case sensitive.
