Stephen Stephen - 7 months ago 13
SQL Question

MYSQL: can find but not replace corrupt characters

I have an odd problem in that I can FIND fields that have the corrupt characters I am looking for, but using REPLACE will affect zero items:

The query below (has corrupt characters after the % that can only been seen in Firefox text fields btw, not Chrome or Safari) WORKS and will return over a thousand records.

SELECT * from resource_data where value like '%';


BUT the following update query affects ZERO items, despite having the exact same like value.

UPDATE resource_data
SET value = REPLACE(value,'%','')
WHERE value like '%';


Any ideas?

FYI, here is what the characters look like in Firefox:notice the corrupt characters? That is what I am cutting and pasting into my query after the %

Answer

If it is hex 1D in latin1, ...

See if this fetches the rows, corrected:

SELECT REPLACE(value, UNHEX('1D'), '')
    FROM resource_data
    WHERE value LIKE CONCAT('%', UNHEX('1D'), '%');

If it shows the row(s), then consider doing

UPDATE resource_data
    SET value = REPLACE(value, UNHEX('1D'), '');