0

I have a field in a table that I found has hidden/invisible \ at the end of each value. This is causing issues in another process that requires the data to be moved over to a Redshift database. The hidden backslashes is causing the data to error in th load. So I'm trying to use the replace function to get rid of them but every combination I try doesn't work.

I have tried the following so far:

UPDATE table SET field = REPLACE(field,'\\','');
UPDATE table SET field = REPLACE(field,'\\\\','');
UPDATE table SET field = REPLACE(field,'\\\\\\','');

The 2nd and 3rd tries were to see if I just needed to escape the backslashes. However, none of them removed the characters as the load still fails due to the backslashes.

I've also tried using char(92) instead of the actual backslash with no luck.I'm not sure what I'm doing wrong at this point.

wnycanesfan
  • 1
  • Perhaps those fields don't contain what you think they contain. Afaik there's no such thing as a 'hidden backslash', but perhaps some other hidden character is being shown as escaped. Perhaps running `HEX()` function on that field and share the output. – Evert Jul 08 '23 at 04:54
  • If you look at the field in the table you see values like XX12345678. The Redshift load errors log is saying the value is XX12345678\\. Now when I run HEX() on the field in MySQL I get the following value 0D. The older data in the field is returning nothing when I run the HEX function on data from a couple of months ago. – wnycanesfan Jul 08 '23 at 11:11

0 Answers0