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)

To remove specified invalid characters from all rows in a table, run the UPDATE command with the TRANSLATE function, as shown in the following example:  

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