SQL Server to MySQL migration, replace ‘xA0’ with a regular space

I am having some trouble while migrating data from SQL Server to MySQL–it seems some data does not want to place nice. I am recieving the error: Incorrect string value: 'xA0xA0' for column 'Address1'

As I am trying to automate data migrations that will happen regularly, I need to find a solution I can put into a script. I am using wbcopytables.exe at the moment, but I do not necessarily need to.

In just trying to find an appropriate solution to later implement in the script, I have tried running the below in SQL Server to fix the source data:

UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'xA0', ' ')

and

UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'u00A0', ' ')

but that is not fixing the source data, and my Google-Fu is failing me. Thoughts?

Maybe you can use CHARINDEX casting your Address1 to varbinary(8000) to identify the offending data:

select * 
from [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
where charindex(0xA0, cast(Address1 as varbinary(8000))) > 0;

Check this answer: https://dba.stackexchange.com/a/37355/55596