CODEWITHSUNDEEP
shellsqlplus
Leandro Toshio Takeda
Leandro Toshio Takeda

Reputation: 1639

How to remove blank spaces from SQLPLUS?

I have a file with this:

set linesize 1000
set trimspool on
set trimout on
set pagesize 0
set feedback off

spool result.csv

SELECT process_id || ';' || file_name || ';' || source || ';' || destination || ';' || type || ';' || transfer_name || ';' || message || ';' || message2  || ';' || destination_sub_group
FROM table
WHERE process_id = '12345';

And SQLPLUS is calling it But this is returning blank spaces, specially message2 field, any idea on how to remove it?

Here is the output:

12345;filename.txt;X;X;4;X;xx =  xxxx

Warning: Using insecure memory!

Decoding data....
Secret key is required to read it.
Key for user ID "X"
Error decrypting file '/apps/egs/gen/file_name.txt.pgp'.
;INBOUND

Thanks!

Here is the output I would like:

12345;filename.txt;X;X;4;X;xx = xxxx Warning: Using insecure memory! Decoding data.... Secret key is required to read it. Key for user ID "X" Error decrypting file /apps/egs/gen/file_name.txt.pgp'.;INBOUND

Upvotes: 0

Views: 26965

Answers (3)

johannes
johannes

Reputation: 21

i fought this problem for days, when i wanted to get the query results into a csv... set mark csv ON ended up solving the problem in that case... took my for ever to find that command

Upvotes: 2

TenG
TenG

Reputation: 4004

Add the following:

COL the_stuff form a1000

Then add the "the_stuff" column alias to your query:

SELECT process_id .... destination_sub_group the_stuff from ..

This will explicitly allow you some control over how this output is displayed.

Next, to deal with the embedded linefeeds in your output, wrap lot around a TRANSLATE ( ..., CHR(10), '+' )

E.g.

This show data with linefeeds:

SQL> select 'line 1' || chr(10) || 'line2' || chr(10) || 'line 3' txt from dual;

TXT
-------------------
line 1
line2
line 3

Translate to replace the linefeeds with "+":

SQL> select translate ( 'line 1' || chr(10) || 'line2' || chr(10) || 'line 3',
                        chr(10), '+' ) txt  
    from dual;

TXT
-------------------
line 1+line2+line 3

SQL>

Upvotes: 0

David Jashi
David Jashi

Reputation: 4511

Try using TRIM to remove trailing and leading spaces and REPLACE to remove linefeeds and carriage returns:

    SELECT process_id || ';' || file_name || ';' || source || ';' || destination || ';' || type || ';' || transfer_name || ';' || message || ';' || 
    replace(replace(trim(message2),CHR(10),' '),CHR(13),' ')  || ';' || destination_sub_group
    FROM table WHERE process_id = '12345';

Upvotes: 0

Related Questions