There are invalid characters in my Amazon Redshift data. How do I remove them?
If your data contains non-printable ASCII characters, such as null, bell, or escape characters, you might have trouble retrieving the data or unloading the data to Amazon Simple Storage Service (Amazon S3). For example, a string that contains a null terminator, such as "abc\0def," will be truncated at the null terminator, resulting in incomplete data.
Use the TRANSLATE function to remove the invalid character. In the following example, the null terminator "\0" is replaced with an empty value, which removes it from the string.
admin@testrs=# select a,translate(a,chr(0),'') from invalidstring; a | translate --------+----------- abc | abcdef abcdef | abcdef (2 rows)
admin@testrs=# select * from invalidstring; a -------- abc abcdef (2 rows) admin@testrs=# update invalidstring set a=translate(a,chr(0),'') where a ilike '%'||chr(0)||'%'; UPDATE 1 admin@testrs=# select * from invalidstring; a -------- abcdef abcdef (2 rows)
Did this page help you? Yes | No
Back to the AWS Support Knowledge Center
Need help? Visit the AWS Support Center
Published: 2018-08-01