We have one sqlserver data source and our RDV is in oracle. If a source value for a char(12) column is 'Saint John`s' and the target column is varchar(12) the load will fail because the ` character (left quote is not ansi) takes up two bytes and exceeds the 12 character limit.
There are a handful of special characters we see repeatedly like that but also some outliers like a smiley face emoticon.
So far our options are:
1. Use nvarchar - stores string as multi byte so doubles the space required
2. Double the varchar length to insure that data doesn’t get lopped off
3. Profile the data and figure out all the special characters and replace them where it makes sense ( e.g. ` -> ') and strip out any that are 'garbage' (smiley face).
4. Any others?
I got pushback when I suggested #3 could be considered a hard rule. What does everyone else think?