remove special character from string in snowpipe copy command in snowflake

Question

Im trying to load data to snowflake table which is having numeric datatypes but the column value contains number with special character 0,0000 due to this copy command is failing. Is their any chance we can handle this in file format instead of handling this in select statement?

Answer 1

There is no option in file format that would handle strings inside of a numeric field. This is the purpose of allowing you to use a SELECT statement in your COPY INTO command to handle simple manipulation.

Your other option would be to load it as a string/varchar during your COPY INTO command, and then handle the casting as a secondary step in your processing. This would likely be faster processing, actually.