title: How to remove Unicode replacement character from Snowflake
body: <p>When loading data to Snowflake using the COPY INTO command, there is an parameter called: REPLACE_INVALID_CHARACTERS.  According to the documentation, if this is set to TRUE, then any invalid UTF-8 characters are replaced with a Unicode replacement character which looks like this (�)</p>
  
  <p><a href="https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html#type-csv" rel="nofollow noreferrer">https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html#type-csv</a></p>
  
  <p>My question is, how can I remove this character from data in my table?  I have tried to use REGEXP_REPLACE but have been unable to figure out the right pattern to use.  </p>
  
  <p>Here is an example of what the data looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/fdrK1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fdrK1.png" alt="Unicode Replacement Character"></a></p>
  
  <p>Notice how the LENGTH function doesn't even register that the character is there since it says there are 7 characters when there are clearly 8.  </p>
  
  <p>Any advice on what Snowflake SQL function to use to remove these characters would be greatly appreciated!</p>