topic Stripping Blanks from SQL*Plus output in Operating System - HP-UX

Stripping Blanks from SQL*Plus output

Michael Campbell — Thu, 04 Nov 2004 05:48:39 GMT

Hi

Does anyone know how to avoid getting blanks at the end of records when spooling to a file using SQL*plus. i.e. if a record ('|' delimited) is spooled as

CL |CLS|CLS4127 |2001123112 |ABCD |6895000000|CAN |GO000| |IEP| 0| | | | |PHXJOB | | | | | | | | | | | | | | |

is there a way to get it to spool as

CL|CLS|CLS4127|2001123112|ABCD|6895000000|CAN|GO000||IEP|0|||||PHXJOB|||||||||||||||

We are using 8.1.7.4

Any Help Appreciated

Michael

Re: Stripping Blanks from SQL*Plus output

Yogeeraj_1 — Thu, 04 Nov 2004 06:14:05 GMT

hi,

try using "replace".

e.g.
YD@MYDB.MU> select
replace(
replace('CL |CLS|CLS4127 |2001123112 |ABCD |6895000000|CAN |GO000| |IEP| 0| | | | |PHXJOB | | | | | | | | | | | | | | |',' |','|'),'| ','|')
from dual;

REPLACE(REPLACE('CL|CLS|CLS4127|2001123112|ABCD|6895000000|CAN|GO000||IEP|0|||||
PHXJ
________________________________________________________________________________
____
CL|CLS|CLS4127|2001123112|ABCD|6895000000|CAN|GO000||IEP|0|||||PHXJOB|||||||||||
||||

hope this helps!

regards
Yogeeraj

Re: Stripping Blanks from SQL*Plus output

Jean-Luc Oudart — Thu, 04 Nov 2004 07:14:11 GMT

write your own function :

create or replace function trim10blank(
InString CHAR
) return CHAR
IS
Begin
if InString = ' ' then
return ' ';
else
return InString;
end if;
end;
####################################
SQLWKS> select 'AA' || trim10blank(' ') || 'ZZ' from dual
2>
'AA'||TRIM10BLANK('')||'ZZ'
--------------------------------------------------------------------------------
AA ZZ
1 row selected.
SQLWKS> select 'AA' || trim10blank(' zz') || 'ZZ' from dual
2>
'AA'||TRIM10BLANK('ZZ')||'ZZ'
--------------------------------------------------------------------------------
AA zzZZ
1 row selected.

Regards
Jean-Luc

Re: Stripping Blanks from SQL*Plus output

Jean-Luc Oudart — Thu, 04 Nov 2004 07:17:07 GMT

As the Web site has some effect in spaces,
I attached solution and output.

Regards
Jean-Luc

Re: Stripping Blanks from SQL*Plus output

Hein van den Heuvel — Thu, 04 Nov 2004 09:42:53 GMT

Or you could post process the spooled file with a simple:

perl -pe 's/ +\|/|/g' input > output

This will remove any trailing spaces from a field, but will not remove spaces in the middle of a field.

-p = loop through input and print $_ to output
-e = program follows on line

s///g = repeated substitutions in the line
" +\|" = one or more spaces followed by a |

hth,
Hein.

Re: Stripping Blanks from SQL*Plus output

Michael Campbell — Thu, 04 Nov 2004 09:47:06 GMT

Hein

Thanks, but the reason I want to strip out the blanks is because the spool file is too big. About 33,000,000 rows would dump to a spool file of around 70Gb.

Regards

Michael

Re: Stripping Blanks from SQL*Plus output

Hein van den Heuvel — Thu, 04 Nov 2004 10:12:41 GMT

Ok, understood. So now it will only be 30GB huh ? :-) :-)

btw... you might still consider a postprocess solution with perl / awk / ed / tr or whatever your preference is, because contrary to a sqlplus solution it can deal with all fields in one whack.
You would just have to make sure not to have the spool file land on the disk, but send it to a pipe.

1) Either with a mknod and have a slave process read teh pipe, transform and output

2) or the classic pipe:

echo "@report" | sqlplus -s user/pass | perl 'transform' | gzip > trimmed-and-compressed.rpt.gz

Cheers,
Hein.