How to remove new line characters from data rows in Presto/AWS Athena?

Question

I'm querying some tables on Athena (Presto SAS) and then downloading the generated CSV file to use locally. Opening the file, I realised the data contains new line characters that doesn't appear on AWS interface, only in the CSV and need to get rid of them. Tried using the function replace(string, search, replace) → varchar to skip the newline char replacing \n for \\n without success:

SELECT
    p.recvepoch, replace(p.description, '\n', '\\n') AS description
FROM
    product p
LIMIT 1000

How can I achieve that?

Answer 1

The problem was that the underlying table data doesn't actually contains \n anywhere, instead, the actual newline character, which is represented by char(10). I was able to achieve the expected behaviour using the replace function passing it as parameter:

SELECT
    p.recvepoch, replace(p.description, chr(10), '\n') AS description
FROM
    product p
LIMIT 1000