Clash Royale CLAN TAG

Escaping New Line character within SQL query

I am reading a table using my java code and creating a csv file out of it. So the 4 rows that I have get converted similar to below -

sam , 18 , banker , He likes to play football
jam , 28 , hacker , he likes nothing


However in certain cases when the last varchar2 column contains n it becomes like this

sam , 18 , banker , He likes
to play
football
jam , 28 , hacker , he
likes
nothing


When I try to read the file , each line is read one at a time and I'm not able parse the data due to few words being pushed to subsequent lines. Is there a way to escape the new line character within the column in my query to make it into a single line?

My sql query

select name , age , job , hobbies from person_details


I am using csvwriter to generate the csv file -

CSVWriter csvWriter = new CSVWriter(new FileWriter(results), DELIMITER, Character.MIN_VALUE, CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.DEFAULT_LINE_END);


I used the below to remove line breaks. However looking for ways to preserve them as well -

replace (replace (hobbies, chr (13), ''), chr (10), ' ')

replace (replace (hobbies, chr (13), ''), chr (10), ' ')

3 Answers
3

Use the REPLACE or TRANSLATE functions:

REPLACE

TRANSLATE

SELECT NAME, AGE, JOB, REPLACE(HOBBIES, CHR(10), '')
FROM PERSON_DETAILS


or

SELECT NAME, AGE, JOB, TRANSLATE(HOBBIES, 'x' || CHR(10) || CHR(12) || CHR(13), 'x')
FROM PERSON_DETAILS -- LF FF CR


TRANSLATE is useful if you have multiple translations to make. Here I've augmented the response to eliminate carriage returns, form feeds, and linefeeds in HOBBIES.

TRANSLATE

Depends on the CSV parser on the other side. They vary a lot in behavior as CSV isn't really standardized. But try to enclose the strings in double quotes.

SELECT '"' || name || '"',
age,
'"' || job || '"',
'"' || hobbies || '"'
FROM person_details;


But JB Nizet's comment has a point. It's probably the best idea to do the transformation in the Java code.

Remove n while querying. Use the following query.

n

select name , age , job , REPLACE(hobbies,CHR(10),'') "hobbies" from person_details


n

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.