How do I query Athena for columns containing a control character?

Question

I need to find rows in which a control character appears in a particular column, but the CHAR function is not available in Athena.

How do I query akin to this?

SELECT * 
FROM table_name 
WHERE column_identifier like '%'||char(31)||'%' 
LIMIT 10;

Answer 1

Wrap the zero-padded decimal representation of the character you want to query for with the chr function, e.g.:

SELECT * 
FROM table_name 
WHERE column_identifier LIKE '%'||chr(0031)||'%' 
LIMIT 10;