CODEWITHSUNDEEP
javasqloracle-databasejava-8
Punter Vicky
Punter Vicky

Reputation: 16992

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), ' ')

Upvotes: 2

Views: 3273

Answers (3)

Pankaj Singhal
Pankaj Singhal

Reputation: 16043

Remove \n while querying. Use the following query. 

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

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Use the REPLACE or TRANSLATE functions:

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.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

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.

Upvotes: 1

Related Questions