newline in single column output (Comma Separated Values)

Toad for Oracle & DevOps Toolkit
1

Hi folks

I have a result with more than 3000 rows and a single column with CSV.
98% is correct, but in 2% i have in CSV-output a [NEWLINE]. How to get
rid of that [NEWLINE]?

SQL

2

You can try using the REPLACE function to remove the carriage return
form feed characters

SELECT REPLACE(YOUR_COLUMN, CHR(13)||CHR(10), ' ')

Ed
[TeamT]

On 9/2/2010 10:15 AM, Sven Aluoor wrote:

Hi folks

I have a result with more than 3000 rows and a single column with CSV.
98% is correct, but in 2% i have in CSV-output a [NEWLINE]. How to get
rid of that [NEWLINE]?

SQL

3

One way (if a sql script) is to:
Make your linesize large enough to take the longest row
set linesize 2000

then set trimspool on so as to remove excess whitespace at the end of each line

New SQL:
– ================================================================
set linesize 2000 – OR MORE IF NECESSARY
set trimspool on

spool outputfile.txt

SELECT d.leaf_ug_name
|| ‘;’
|| a.description
|| ‘;’
|| a.notes
|| ‘;’
|| b.user_id
|| ‘;’
|| c.FIRSTNAME
|| ‘;’
|| c.LASTNAME
|| ‘;’
|| c.DEPARTMENT
|| ‘;’
FROM essdb.CTSA_USER_GROUP_OpenLDAP a,
essdb.ru_ug b,
essdb.uam_ent_user c,
essdb.user_group d
WHERE a.ug_name = b.ug_name
AND d.ug_name = a.ug_name
AND c.user_id = b.user_id
AND a.rss_name = ‘openldap’
AND a.ug_name LIKE ‘CN=RO%OU=X2201_OperRG,OU=OpRoles,OU=Z23I,DC=openldap,DC=netcom’
/

spool off
/
– ================================================================

4

Make your linesize large enough to take the longest row

…

set trimspool on so as to remove excess whitespace

That won’t work in the event the column value itself contains a carriage
return. You’re still going to get the extra values on a new line.

I was under the impression the original query meant the value itself had a
carriage return, but you may very well be correct that the few lines that are
failing are just a little bit longer and a simple linesize change would be
sufficient to correct the issue.

Roger S.

5

And I just saw Ed’s post and went ‘oh yeah…’ thinking as you both did so a combination of set linesize and replace chr(10) or chr(13) will cover both possibilities…

6

On Thu, Sep 2, 2010 at 4:45 PM, Ed Klinger wrote:

You can try using the REPLACE function to remove the carriage return
form feed characters

SELECT REPLACE(YOUR_COLUMN, CHR(13)||CHR(10), ' ')

Hi Ed

thanks for you hint, but "SELECT REPLACE(YOUR_COLUMN,
CHR(13)||CHR(10), ' ')" doesn't work at me.

I used:

select dump(field, field) from table;

then (after knowing that's a line feed):

select REPLACE (field,CHR(10),' ')

cheers Sven

7

Hey Sven,

I have a result with more than 3000 rows and a single column with CSV.
98% is correct, but in 2% i have in CSV-output a [NEWLINE]. How to get
rid of that [NEWLINE]?

This is a test case I tried in 10gR1 that had a promising result:

create table aaa (bbb varchar2(20));

insert into aaa (bbb) values ('A1');
insert into aaa (bbb) values ('B2');
insert into aaa (bbb) values ('C'||chr(10)||'3');
insert into aaa (bbb) values ('D4');
insert into aaa (bbb) values ('E5');

commit;

select regexp_replace(bbb,'[[:cntrl:]]','') bbb2
from aaa;

...but the success of the REGEXP_REPLACE depends on the true nature of your
data and potentially the version of Oracle you're using (10g would be fine).

You'll need to experiment with it to determine if the "[[:cntrl:]]" pattern
in the REGEXP_REPLACE is grabbing more characters than what you'd like.
Have you used the DUMP() function before? It's great to help debug issues
like this.

HTH! GL!

Rich -- [TeamT]

Disclaimer: Are you ready for some football?