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:
|
1 2 3 4 5 6 |
SELECT p.recvepoch, replace(p.description, '\n', '\\n') AS description FROM product p LIMIT 1000 |
How can I achieve that?
Answer:
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:
|
1 2 3 4 5 6 |
SELECT p.recvepoch, replace(p.description, chr(10), '\n') AS description FROM product p LIMIT 1000 |